SQL生成数据字典语句

时　间:2019-08-02 08:31:16
作　者:缪炜   ID：24010  城市：江阴
摘　要:SQL生成数据字典语句
正　文:

Select   [表名] = c.name,
         [表说明] = Isnull(f.[value],''),
         [列名] = a.name,
         [列序号] = a.column_id,
         [标识] = CASE 
                  WHEN is_identity = 1 THEN '√'
                  ELSE ''
                END,
         [主键] = CASE 
                  WHEN EXISTS (Select 1
                               FROM   sys.objects x
                                      JOIN sys.indexes y
                                        ON x.TYPE = N'PK'
                                           AND x.name = y.name
                                      JOIN sysindexkeys z
                                        ON z.id = a.object_id
                                           AND z.indid = y.index_id
                                           AND z.colid = a.column_id) THEN '√'
                  ELSE ''
                END,
         [类型] = b.name,
         [字节数] = CASE 
                   WHEN a.[max_length] = -1
                        AND b.name != 'xml' THEN 'max/2G'
                   WHEN b.name = 'xml' THEN '2^31-1字节/2G'
                   ELSE Rtrim(a.[max_length])
                 END,
         [长度] = CASE 
                  WHEN Columnproperty(a.object_id,a.name,'Precision') = -1 THEN '2^31-1'
                  ELSE Rtrim(Columnproperty(a.object_id,a.name,'Precision'))
                END,
         [小数] = Isnull(Columnproperty(a.object_id,a.name,'Scale'),
                       0),
         [是否为空] = CASE 
                    WHEN a.is_nullable = 1 THEN '√'
                    ELSE ''
                  END,
         [列说明] = Isnull(e.[value],''),
         [默认值] = Isnull(d.text,'')
FROM     sys.columns a
         LEFT JOIN sys.types b
           ON a.user_type_id = b.user_type_id
         INNER JOIN sys.objects c
           ON a.object_id = c.object_id
              AND c.TYPE = 'U'
         LEFT JOIN syscomments d
           ON a.default_object_id = d.id
         LEFT JOIN sys.extended_properties e
           ON e.major_id = c.object_id
              AND e.minor_id = a.column_id
              AND e.class = 1
         LEFT JOIN sys.extended_properties f
           ON f.major_id = c.object_id
              AND f.minor_id = 0
              AND f.class = 1
orDER BY c.name