Search

Monday, May 23, 2011

Get Detailed Table and Column Information in SQL



There is often some basic information that you want to see regarding the tables and columns in your current database.

Select t.TABLE_NAME As [Table], 
c.COLUMN_NAME As [Column],
c.DATA_TYPE As [DataType],
c.CHARACTER_MAXIMUM_LENGTH As [Length],
Case
When c.IS_NULLABLE = 'Yes'
Then 1
Else 0
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'
And t.TABLE_NAME
Not In ('sysdiagrams')
Order By t.TABLE_NAME, c.ORDINAL_POSITION

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.

No comments:

Post a Comment