sql server 获取数据库表的字段和字段属性源码

--select name from PlatFormEF.sys.tables  --获取某个数据中的所有表
SELECT d.name AS TableName,  
    A.ColOrder,
    A.Name AS ColName,
    (CASE WHEN COLUMNPROPERTY(a.id, a.name, 'IsIdentity')=1 THEN 1 ELSE 0 END) IsIdentity,
    (CASE WHEN (
                SELECT COUNT(1) FROM SYSOBJECTS WHERE (
                        name IN (SELECT name FROM SYSINDEXES WHERE (id = a.id) AND
                        (indid IN  (SELECT indid FROM SYSINDEXKEYS WHERE (id = a.id) AND
                        (colid in (SELECT colid FROM syscolumns WHERE (id = a.id) AND (name = a.name)))))))
                        AND (xtype = 'PK')
                        )
          >0 THEN 1 ELSE 0 END) IsKey,
    B.Name AS ColDataType,                                          -- 数据类型
    A.Length AS Bytes,                                              -- 占用存储空间
    COLUMNPROPERTY(a.id,a.name,'PRECISION') AS Length,              -- 字符串长度
    ISNULL(COLUMNPROPERTY(a.id, a.name,'Scale'),0) AS ScaleSize,    -- 小数位数
    A.IsNullable,                                                   -- 允许空
    ISNULL(e.text,'') AS DefaultValue,
    ISNULL(g.[value], ' ') AS Remark
FROM  SYSCOLUMNS A
LEFT JOIN SYSTYPES B ON a.xtype=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.class and f.minor_id=0
WHERE B.NAME IS NOT NULL
--WHERE d.name='要查询的表' --如果只查询指定表,加上此条件
ORDER BY A.id, a.ColOrder