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.