在SQL Server2005以上版本中查看数据表的信息

在SQL Server2005以上版本中查看数据表的信息，参考下面的代码，只需要将语句中TableName修改为相应数据库中的表名即可。

SELECT   
表名        =   CASE WHEN A.COLORDER=1 THEN D.NAME ELSE ' ' END, 
--表说明      =   CASE   WHEN   A.COLORDER=1   THEN   ISNULL(F.VALUE, ' ')   ELSE   ' '   END, 
字段序号    =   A.COLORDER, 
字段名      =   A.NAME, 
--标识        =   CASE WHEN COLUMNPROPERTY(A.ID,A.NAME, 'ISIDENTITY ')=1 THEN '√' ELSE ' '   END, 
--主键        =   CASE   WHEN   EXISTS(SELECT   1   FROM   SYSOBJECTS   WHERE   XTYPE= 'PK '   AND   PARENT_OBJ=A.ID   AND   NAME   IN   ( 
--SELECT NAME   FROM   SYSINDEXES   WHERE   INDID   IN( 
--SELECT INDID   FROM   SYSINDEXKEYS   WHERE   ID   =   A.ID   AND   COLID=A.COLID)))   THEN   '√'   ELSE   ' '   END, 
类型          =   B.NAME, 
--占用位元数组  =   A.LENGTH, 
--长度          =   COLUMNPROPERTY(A.ID,A.NAME, 'PRECISION '), 
--小数位数      =   ISNULL(COLUMNPROPERTY(A.ID,A.NAME, 'SCALE '),0), 
--允许空        =   CASE   WHEN   A.ISNULLABLE=1   THEN   '√'ELSE   ' '   END, 
--预设值        =   ISNULL(E.TEXT, ' '), 
字段说明      =   ISNULL(G.[VALUE], ' ') 
FROM   
SYSCOLUMNS A LEFT JOIN SYSTYPES B ON A.XUSERTYPE=B.XUSERTYPE 
INNER JOIN SYSOBJECTS D ON A.ID=D.ID AND D.XTYPE= 'U ' AND D.NAME <> 'DTPROPERTIES ' 
LEFT JOIN SYSCOMMENTS E ON A.CDEFAULT=E.ID 
LEFT JOIN sys.extended_properties G ON A.ID=G.major_id AND A.COLID=G.minor_id     
LEFT JOIN sys.extended_properties F ON D.ID=F.major_id   AND   F.minor_id=0 
where d.name = 'TableName'
ORDER BY A.ID,A.COLORDER