SQL生成数据字典语句-will.miao
Access软件网QQ交流学习群(群号码198465573),欢迎您的加入!
首页 >技术文章> ADP及SQL SERVER


SQL生成数据字典语句

发表时间:2019/8/2 8:31:16 评论(0) 浏览(6299)  评论 | 加入收藏 | 复制
   
摘 要: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

Access软件网交流QQ群(群号:198465573)
 
 相关文章
SQL Server数据库字典SQL语句  【UMVsoft整理  2008/6/21】
公司客户管理系统\支持按字典标签ABC字母查询的示例  【lyxiong0728  2010/11/6】
【access小品】康熙字典---拼音变字母函数  【煮江品茶  2011/4/26】
[access查询]按拼音查询 宏鹏老师《access数据类型词汇...  【蒋元根  2011/8/30】
【Access小品】事必做于细--字典的运用示例  【煮江品茶  2014/1/9】
使用字典浅谈  【缪炜  2018/10/19】
常见问答
技术分类
相关资源
文章搜索
关于作者

will.miao

文章分类

文章存档

友情链接