In order to help with documentation of MS-SQL databases, I wrote this small ColdFusion utility to produce the table schemas from a given database. It is very simple and easy to install. I just recommend you to use it only on your development server or localhost, without external access, otherwise you will be supplying information to hackers.

It is composed of one ColdFusion template (CFM) and a component (CFC).

There is only one required URL attribute to be passed and 3 optional ones:

  • datasource (required): datasource (registered in CF admin)
  • table (optional): table name
  • fList (optional): list of columns you want to show
  • fLabels (optional): labels for the columns

If you omit the table attribute, the utility will list all tables in the database.

You may download the code here.

Here is the CFM that calls the component, you may call it dbSchema.cfm:
<cfparam name="url.datasource" default="" />
<cfparam name="url.table" default="" />
<cfscript>
if (len(trim(url.datasource))) {
cf = createObject("component", "ws.dbSchema");
result = cf.getSchema(url.datasource, url.table);
if (result.status) {
result = cf.showSchema(result.content);
} else writeOutput(result.content);
} else writeOutput("Missing DATASOURCE attribute!");
</cfscript>

Here is the component, you may call it dbSchema.cfc:
<cfcomponent displayname="dbSchema" hint="Display table schemas" output="true">
<cffunction name="getSchema" access="remote" output="false" returntype="any">
<cfargument name="datasource" type="string" required="true" />
<cfargument name="tableName" type="string" required="false" default="" />
<cfset result = structNew() />
<cfset result.content = "" />
<cfset result.status = true />
<cftry>
<cfquery name="result.content" datasource="#arguments.datasource#">
SELECT
TABLE_NAME
, COLUMN_NAME
, COLUMN_DEFAULT
, lower(IS_NULLABLE) as IS_NULLABLE
, DATA_TYPE
, CHARACTER_MAXIMUM_LENGTH
, CHARACTER_OCTET_LENGTH
, COLLATION_CATALOG
, COLLATION_SCHEMA
, COLLATION_NAME
, NUMERIC_PRECISION
, NUMERIC_PRECISION_RADIX
, NUMERIC_SCALE
, DATETIME_PRECISION
, ORDINAL_POSITION
, CHARACTER_SET_CATALOG
, CHARACTER_SET_SCHEMA
, CHARACTER_SET_NAME
, DOMAIN_CATALOG
, DOMAIN_SCHEMA
, DOMAIN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
<cfif len(trim(arguments.tableName))>
WHERE TABLE_NAME = '#arguments.tableName#'
</cfif>
ORDER BY TABLE_NAME, ORDINAL_POSITION
</cfquery>
<cfcatch>
<cfset result.status = false />
<cfset result.content = cfcatch.message & "<br/>" & cfcatch.detail />
</cfcatch>
</cftry>
<cfreturn result />
</cffunction>
<cffunction name="showSchema" access="remote" output="true" returntype="Any">
<cfargument name="q" type="query" required="true" />
<cfargument name="fList" type="string" required="false" default="ORDINAL_POSITION, COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,NUMERIC_PRECISION, NUMERIC_SCALE,COLUMN_DEFAULT,IS_NULLABLE, CHARACTER_SET_NAME, COLLATION_NAME" />
<cfargument name="fLabels" type="string" required="false" default="##,Column,Type, Max.Length,Precision,Scale,Default,Null,Charset,Collation" />
<cfset tableName = "" />
<table border="0" width="100%" align="center" cellpadding="2" cellspacing="1" style="border:1px solid black; font-family:arial; font-size:10pt;">
<cfoutput>
<tr bgcolor="black">
<td colspan="#listLen(fLabels)#">
<div style="display:inline">
<div align="left" style="font-family:arial;font-size:14pt;font-weight:bold;color:white;">
db schema ver 1.0</div>
<div align="right" style="font-family:arial; font-size:8pt; font-weight:bold; color:white; text-align:right;">
by Ricardo Parente</div>
</div>
</td>
</tr>
<cfloop query="q">
<cfif q.table_name is not tableName>
<cfset showHeader(q.table_name, fLabels) />
</cfif>
<cfset bgcolor = iif(q.currentRow mod 2, de("efefef"), de("fefefe")) />
<tr bgcolor="###bgcolor#">
<cfloop from="1" to="#listLen(fList)-1#" index="x">
<td style="border-right:1px dashed grey; padding:0 2px 0 5px;">
#evaluate("q." & listGetAt(fList, x))# </td>
</cfloop>
<td style="padding:0 2px 0 5px;">
#evaluate("q." & listLast(fList))# </td>
</tr>
<cfset tableName = q.table_name />
</cfloop>
</cfoutput>
</table>
</cffunction>
<cffunction name="showHeader" access="private" output="true" returntype="Any">
<cfargument name="header" type="string" required="true" />
<cfargument name="fLabels" type="string" required="false" default="Column,Type,Max.Length,Default,Null,Collation" />
<cfoutput>
<tr>
<th colspan="#listLen(fLabels)#" style="font-weight:bold; font-size:14pt; color:maroon;" bgcolor="orange"><cfoutput>#ucase(header)#</cfoutput></th>
</tr>
<tr bgcolor="##a0a0a0">
<cfloop from="1" to="#listLen(fLabels)#" index="x">
<th>#listgetAt(fLabels, x)#</th>
</cfloop>
</tr>
</cfoutput>
</cffunction>
</cfcomponent>

2 thoughts on “MS-SQL Database Table Schema

  1. Hi Ricardo. Thanks for sharing your code. If you are on cf8, it is worth looking at the cfdbinfo tag. Also, don’t forget to var scope you variables inside functions 🙂

Leave a Reply