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.