There is often some basic information that you want to see regarding the tables and columns in your current database. You don’t want to fuss with the INFORMATION_SCHEMA or SYS views to join it all together either. So, I decided to spend a few minutes to get together the things that I use the most into a single query and post it here for everyone.
Select t.TABLE_NAME As [Table],
c.COLUMN_NAME As [Column],
c.DATA_TYPE As [DataType],
c.CHARACTER_MAXIMUM_LENGTH As [Length],
When c.IS_NULLABLE = 'Yes'
End As [Nullable],
Left(k.CONSTRAINT_NAME, 2) As [KeyType],
d.Description As [MS_Description]
From INFORMATION_SCHEMA.TABLES t
Inner Join INFORMATION_SCHEMA.COLUMNS c
On t.TABLE_NAME = c.TABLE_NAME
Left Outer Join INFORMATION_SCHEMA.KEY_COLUMN_USAGE k
On t.TABLE_NAME = k.TABLE_NAME
And c.COLUMN_NAME = k.COLUMN_NAME
Left Outer Join (Select o.Name As [Table],
c.name As [Column],
ep.value As [Description]
From sys.objects o
Inner Join sys.extended_properties ep
On o.object_id = ep.major_id
LEFT Join syscolumns c
On ep.minor_id = c.colid
And ep.major_id = c.id
Where o.type = 'U') As d
On t.TABLE_NAME = d.[Table]
And c.COLUMN_NAME = d.[Column]
Where t.TABLE_TYPE = 'BASE TABLE'
Not In ('sysdiagrams')
Order By t.TABLE_NAME,
I hope this saves everyone else as much time as it saves me! It also includes the MS_Description schema property in case you document your databases in hopes that .NET will support a self-documenting database architecture some day.
If you have any questions or comments on how I can make this script better, please feel free to comment on this post.