Using OBJECT CATALOG VIEWS

SELECT  T.NAME AS [TABLE NAME], C.NAME AS [COLUMN NAME], P.NAME AS [DATA TYPE], P.MAX_LENGTH AS[SIZE],   CAST(P.PRECISION AS VARCHAR) +‘/‘+ CAST(P.SCALE AS VARCHAR) AS [PRECISION/SCALE]
FROM SYS.OBJECTS AS T
JOIN SYS.COLUMNS AS C
ON T.OBJECT_ID=C.OBJECT_ID
JOIN SYS.TYPES AS P
ON C.SYSTEM_TYPE_ID=P.SYSTEM_TYPE_ID
WHERE T.TYPE_DESC=‘USER_TABLE‘;

Using INFORMATION SCHEMA VIEWS

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION,
       COLUMN_DEFAULT, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH,
       NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE,
       DATETIME_PRECISION
FROM INFORMATION_SCHEMA.COLUMNS

LINKS:

http://dbalink.wordpress.com/2008/10/24/querying-the-object-catalog-and-information-schema-views/

http://*.com/questions/420741/getting-list-of-tables-and-fields-in-each-in-a-database

T-SQL获取数据库中的Table表结构信息，Column结构信息,布布扣,bubuko.com

T-SQL获取数据库中的Table表结构信息，Column结构信息