Describe SQL Server Tables

DescribeTables.sql

-- Table descriptions
SELECT DISTINCT u.name + '.' + t.name AS [Table], td.value AS TableDescription
FROM         sys.sysobjects AS t INNER JOIN
                      sys.sysusers AS u ON u.uid = t.uid LEFT OUTER JOIN
                      sys.extended_properties AS td ON td.major_id = t.id AND td.minor_id = 0 AND td.name = 'MS_Description' INNER JOIN
                      sys.syscolumns AS c ON c.id = t.id LEFT OUTER JOIN
                      sys.extended_properties AS cd ON cd.major_id = c.id AND cd.minor_id = c.colid AND cd.name = 'MS_Description'
WHERE     (t.type = 'u')

-- Column descriptions
SELECT     TOP (100) PERCENT u.name + '.' + t.name AS [Table], td.value AS TableDescription, c.name AS [Column], cd.value AS ColumnDescription
FROM         sys.sysobjects AS t INNER JOIN
                      sys.sysusers AS u ON u.uid = t.uid LEFT OUTER JOIN
                      sys.extended_properties AS td ON td.major_id = t.id AND td.minor_id = 0 AND td.name = 'MS_Description' INNER JOIN
                      sys.syscolumns AS c ON c.id = t.id LEFT OUTER JOIN
                      sys.extended_properties AS cd ON cd.major_id = c.id AND cd.minor_id = c.colid AND cd.name = 'MS_Description'
WHERE     (t.type = 'u')
ORDER BY t.name, c.colorder

-- another way to describe
SELECT a.table_name as [Table], a.COLUMN_NAME as [Column], a.IS_NULLABLE as [Nullable], a.DATA_TYPE as [Data Type], a.CHARACTER_MAXIMUM_LENGTH as [Length], a.CONSTRAINT_TYPE as [Constraint], b.PropertyValue as [Description] from
(SELECT
INFORMATION_SCHEMA.COLUMNS.TABLE_NAME
,INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME
,INFORMATION_SCHEMA.COLUMNS.IS_NULLABLE
,INFORMATION_SCHEMA.COLUMNS.DATA_TYPE
,INFORMATION_SCHEMA.COLUMNS.CHARACTER_MAXIMUM_LENGTH
,INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE
from INFORMATION_SCHEMA.COLUMNS
left outer join INFORMATION_SCHEMA.KEY_COLUMN_USAGE
on INFORMATION_SCHEMA.KEY_COLUMN_USAGE.COLUMN_NAME = INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME
and INFORMATION_SCHEMA.KEY_COLUMN_USAGE.TABLE_NAME = INFORMATION_SCHEMA.COLUMNS.TABLE_NAME
and INFORMATION_SCHEMA.KEY_COLUMN_USAGE.TABLE_CATALOG = INFORMATION_SCHEMA.COLUMNS.TABLE_CATALOG
and INFORMATION_SCHEMA.KEY_COLUMN_USAGE.ORDINAL_POSITION = INFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION
left outer join INFORMATION_SCHEMA.TABLE_CONSTRAINTS on
INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME = INFORMATION_SCHEMA.KEY_COLUMN_USAGE.TABLE_NAME
and INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME = INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_NAME) a
left join
(SELECT        o.Name AS ObjectName,
            ep.value AS PropertyValue,
            c.name AS ColumnName
FROM        sys.objects o INNER JOIN sys.extended_properties ep
            ON o.object_id = ep.major_id
            INNER JOIN sys.schemas s
            ON o.schema_id = s.schema_id
            LEFT JOIN syscolumns c
            ON ep.minor_id = c.colid
            AND ep.major_id = c.id
WHERE        o.type IN ('V', 'U', 'P'))  b
on (a.COLUMN_NAME = b.ColumnName) and (a.TABLE_NAME = b.ObjectName)
where a.TABLE_NAME <> 'sysdiagrams'
order by a.TABLE_NAME