SQL获取表结构的字段说明和结构

SELECT 
TableName = OBJECT_NAME(c.object_id), 
ColumnsName = c.name, 
Description = ex.value, 
ColumnType=t.name, 
Length=c.max_length 
FROM 
sys.columns c 
LEFT OUTER JOIN 
sys.extended_properties ex 
ON 
ex.major_id = c.object_id 
AND ex.minor_id = c.column_id 
AND ex.name = 'MS_Description' 
left outer join 
systypes t 
on c.system_type_id=t.xtype 
WHERE 
OBJECTPROPERTY(c.object_id, 'IsMsShipped')=0 
AND OBJECT_NAME(c.object_id) ='DEC_I_HEAD' --表名

还有另一种方法

SELECT  Sysobjects.name AS TABLE_NAME ,
        syscolumns.Id ,
        syscolumns.name AS COLUMN_NAME ,
        systypes.name AS DATA_TYPE ,
        syscolumns.length AS CHARACTER_MAXIMUM_LENGTH ,
        sys.extended_properties.[value] AS COLUMN_DESCRIPTION ,
        syscomments.text AS COLUMN_DEFAULT ,
        syscolumns.isnullable AS IS_NULLABLE
FROM    syscolumns
        INNER JOIN systypes ON syscolumns.xtype = systypes.xtype
        LEFT JOIN sysobjects ON syscolumns.id = sysobjects.id
        LEFT OUTER JOIN sys.extended_properties ON ( sys.extended_properties.minor_id = syscolumns.colid
                                                     AND sys.extended_properties.major_id = syscolumns.id
                                                   )
        LEFT OUTER JOIN syscomments ON syscolumns.cdefault = syscomments.id
WHERE   syscolumns.id IN ( SELECT   id
                           FROM     SYSOBJECTS
                           WHERE    xtype = 'U' )
        AND ( systypes.name <> 'sysname' ) AND Sysobjects.name='DEC_I_HEAD' -- 表名称
ORDER BY syscolumns.colid