快速生成SqlServer数据字典

SELECT   表名 = CASE WHEN A.colorder = 1 THEN D.name ELSE '' END,
         字段序号 = A.colorder,
         字段名 = A.name,
         字段说明 = isnull(G.[value], ''),
         自增 = 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, '')
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='OrderInfo'    --如果只查询指定表,加上此条件
where A.Name not in('CreationTime','CreatorUserId','LastModificationTime','LastModifierUserId','IsDeleted','DeleterUserId','DeletionTime')
ORDER BY A.id, A.colorder