Access开发培训
网站公告
·Access专家课堂QQ群号:151711184    ·Access快速开发平台下载地址及教程    ·欢迎加入Access专家课堂微信群!    ·如何快速搜索本站文章|示例|资料    
您的位置: 首页 > 技术文章 > ADP及SQL SERVER

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


Access软件网QQ交流群 (群号:54525238)       Access源码网店

常见问答:

技术分类:

相关资源:

专栏作家

关于我们 | 服务条款 | 在线投稿 | 友情链接 | 网站统计 | 网站帮助