Viewing table's field properties in a browser 1

[deepatpaul]

Is there some particular SQL syntax I can enter to have a specific table's field properties display in a browser (from a MS SQL 2000 database)? One example is to view the size, field name and any other attributes of field 'fFoobar' in table 'tWhatever'.

 

[Sarky78]

There was a custom tag that i saw a while ago that did this for you, but for the life of me i can't find it at the moment.

This will get you on your way though:

<CFQUERY DATASOURCE="#Request.DSN#" NAME="qryTblDesc">
	SELECT so.Name as TableName,
		   sc.Name as ColumnName, sc.Length,
 		   st.Name as ColumnType
	FROM (sysobjects so INNER JOIN syscolumns sc ON so.ID=sc.ID) INNER JOIN SysTypes st ON sc.xtype=st.xtype
	WHERE so.xType = 'U'
	ORDER BY TableName ASC, ColumnName ASC
</CFQUERY>

<CFSET DisplayTableName = "">
<table border="0" cellpadding="0" cellspacing="0">
	<CFLOOP QUERY="qryTblDesc">
		<CFIF CompareNoCase(Trim(qryTblDesc.TableName), variables.DisplayTableName) NEQ 0>
			<CFSET variables.DisplayTableName = Trim(qryTblDesc.TableName)>
			<tr>
				<td><strong><CFOUTPUT>#variables.DisplayTableName#</CFOUTPUT></strong></td>
			</tr>
		</CFIF>
		<tr>
			<td><CFOUTPUT>#qryTblDesc.ColumnName#&nbsp;&nbsp;&nbsp;#qryTblDesc.Length#&nbsp;&nbsp;&nbsp;#qryTblDesc.ColumnType#</CFOUTPUT></td>
		</tr>
	</CFLOOP>
</table>

This will give you the column type, and size of all of the user defined tables that are within your database

Hope this helps!

Tony

 

[deepatpaul]

Thank you so much! This is exactly what I was looking for.