1. Query
-----code:start----------------------------------------------------
SELECT
TAB.NAME AS Table_Name
,COL.NAME AS Column_Name
,TIP.NAME AS Type_Data
,COL.MAX_LENGTH AS Length_Column,
CASE
WHEN COL.IS_NULLABLE = '0' THEN 'NO'
ELSE 'YES'
END AS Accept_Null
,EXT.VALUE AS Description
FROM SYS.TABLES TAB
INNER JOIN SYS.COLUMNS COL
ON TAB.OBJECT_ID = COL.OBJECT_ID
INNER JOIN SYS.TYPES TIP
ON TIP.USER_TYPE_ID = COL.USER_TYPE_ID
LEFT OUTER JOIN
SYS.EXTENDED_PROPERTIES EXT
ON EXT.MAJOR_ID = TAB.OBJECT_ID AND COL.COLUMN_ID = EXT.MINOR_ID
ORDER BY 1
-----code:end----------------------------------------------------
2. Example Results Messages
Source:
http://www.sqlservercentral.com/scripts/Administration/74760/
Article: Get information from tables