Documenting SQL Server Tables and Columns

Both SQL Server 2000 and 2005 feature a ‘Generate Scripts’ function, which lets you create the DDL statements to generate the CREATE TABLE statements of your database.

While this functionality is quite useful, it does not provide all the available information at one glance. For example, DEFAULT constraint definitions and Foreign Key definitions are separated from the Create Table statement as Alter Table statements further down the text.

Therefore I created a SELECT statement to display all relevant information on a data model in a simple list: table name, column name, datatype, nullability, identity, default and foreign key constraints.

Here it is:

SELECT AS TableName, AS ColumnName, +
	CASE 	WHEN LIKE 'n%char'
			THEN ' (' + CONVERT(nvarchar, col.length / 2) + ')'
		WHEN LIKE '%char%'
			THEN ' (' + CONVERT(nvarchar, col.length) + ')'
		ELSE ''
	END AS DataType,
	CASE col.isnullable WHEN 0 THEN 'NOT NULL' ELSE '' END AS Nullable,
		WHEN conobj.type = 'D' THEN 'DEFAULT ' + syscomments.text
		WHEN conobj.type = 'F' THEN 'REFERENCES ' + + '.' +
		ELSE ''
	END AS Constraints
FROM	sys.sysobjects AS obj
INNER JOIN sys.syscolumns AS col ON =
INNER JOIN sys.systypes ON sys.systypes.xtype = col.xtype
LEFT OUTER JOIN sys.sysconstraints AS con
	INNER JOIN sys.sysobjects AS conobj ON con.constid =
	ON = AND con.colid = col.colid
LEFT OUTER JOIN sys.syscomments ON =
LEFT OUTER JOIN sys.sysforeignkeys AS fk
	INNER JOIN sys.syscolumns AS fkcol
		ON fk.rkeyid = AND fk.rkey = fkcol.colid
	INNER JOIN sys.sysobjects AS fkobj ON =
	ON = fk.constid AND = fk.fkeyid AND col.colid = fk.fkey
WHERE	(obj.type = 'U')
AND 	( <> 'sysname')
ORDER BY, col.colid

5 Responses to Documenting SQL Server Tables and Columns

  1. Roger says:

    I tried this query out in the SQL Server Management Studio, using the Northwind db. However, I get this error:

    Msg 208, Level 16, State 1, Line 2
    Invalid object name ‘sys.sysobjects’.
    Msg 208, Level 16, State 1, Line 2
    Invalid object name ‘sys.syscolumns’.
    … etc.

    Any tips on where/how I should execute this query?


  2. Roger says:

    Never mind. I figured out the problem.

    I was running this for a SQL 2000 server, not SQL 2005. So, I had use ‘sysobjects…’ instead of ‘sys.sysobjects…’.

    It works now.


  3. devio says:

    thanks for pointing that out. SQL2000 does not support the sys. schema

  4. Clint says:

    Great query Devio – worked perfectly for me on SQL 2000, thanks to Roger’s tip.

  5. crywaltRywalt says:

    You have saved me eons of work with this. Thanks so very much.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: