• 生成数据字典


    --方式一
    
    -- 数据字典
    SELECT
            (case when a.colorder=1 then d.name else '' end)表名,
            a.colorder 字段序号,
            a.name 字段名,
            (case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then ''else '' end) 标识,
           (case when (SELECT count(*)
            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 '' else '' end) 主键,
           b.name 类型,
           a.length 占用字节数,
          COLUMNPROPERTY(a.id,a.name,'PRECISION') as 长度,
          isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as 小数位数,
          (case when a.isnullable=1 then ''else '' end) 允许空,
          isnull(e.text,'') 默认值,
          isnull(g.[value],'') AS 字段说明
    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.major_id
    order by a.id,a.colorder
    
    
    
    
    
    --方式二
    
    Set nocount on
    DECLARE @TableName nvarchar(35)
    DECLARE Tbls CURSOR
    FOR
        Select distinct Table_name
        FROM INFORMATION_SCHEMA.COLUMNS
        --put any exclusions here
        --where table_name not like '%old'
        order by Table_name
    OPEN Tbls
    PRINT '<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">'
    PRINT '<html xmlns="http://www.w3.org/1999/xhtml">'
    PRINT '<head>'
    PRINT '<meta http-equiv="Content-Type" content="text/html; charset=UTF8">'
    PRINT '<title>数据库字典</title>'
    PRINT '<style type="text/css">'
    PRINT 'body{margin:0; font:11pt "arial", "微软雅黑"; cursor:default;}'
    PRINT '.tableBox{margin:10px auto; padding:0px; 1000px; height:auto; background:#FBF5E3; border:1px solid #45360A}'
    PRINT '.tableBox h3 {font-size:12pt; height:30px; line-height:30px; background:#45360A; padding:0px 0px 0px 15px; color:#FFF; margin:0px; text-align:left }'
    PRINT '.tableBox table {1000px; padding:0px }'
    PRINT '.tableBox th {height:25px; border-top:1px solid #FFF; border-left:1px solid #FFF; background:#F7EBC8; border-right:1px solid #E0C889; border-bottom:1px solid #E0C889 }'
    PRINT '.tableBox td {height:25px; padding-left:10px; border-top:1px solid #FFF; border-left:1px solid #FFF; border-right:1px solid #E0C889; border-bottom:1px solid #E0C889 }'
    PRINT '</style>'
    PRINT '</head>'
    PRINT '<body>'
    FETCH NEXT FROM Tbls
    INTO @TableName
    WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT '<div class="tableBox">'
        Select '<h3>' + @TableName + ' : '+cast(Value as varchar(1000)) + '</h3>'
        FROM sys.extended_properties A
        WHERE A.major_id = OBJECT_ID(@TableName)
        and name = 'MS_Description' and minor_id = 0
        PRINT '<table cellspacing="0">'
        --Get the Description of the table
        --Characters 1-250
        PRINT '<tr>' --Set up the Column Headers for the Table
        PRINT '<th>字段名称</th>'
        PRINT '<th>描述</th>'
        PRINT '<th>主键</th>'
        PRINT '<th>外键</th>'
        PRINT '<th>类型</th>'
        PRINT '<th>长度</th>'
        PRINT '<th>数值精度</th>'
        PRINT '<th>小数位数</th>'
        PRINT '<th>允许为空</th>'
        PRINT '<th>计算列</th>'
        PRINT '<th>标识列</th>'
        PRINT '<th>默认值</th>'
        --Get the Table Data
        SELECT '</tr><tr>',
        '<td>' + CAST(clmns.name AS VARCHAR(35)) + '</td>',
        '<td>' + ISNULL(CAST(exprop.value AS VARCHAR(500)),'') + '</td>',
        '<td>' + CAST(ISNULL(idxcol.index_column_id, 0)AS VARCHAR(20)) + '</td>',
        '<td>' + CAST(ISNULL(
        (SELECT TOP 1 1
        FROM sys.foreign_key_columns AS fkclmn
        WHERE fkclmn.parent_column_id = clmns.column_id
        AND fkclmn.parent_object_id = clmns.object_id
        ), 0) AS VARCHAR(20)) + '</td>',
        '<td>' + CAST(udt.name AS CHAR(15)) + '</td>' ,
        '<td>' + CAST(CAST(CASE WHEN typ.name IN (N'nchar', N'nvarchar') AND clmns.max_length <> -1
        THEN clmns.max_length/2
        ELSE clmns.max_length END AS INT) AS VARCHAR(20)) + '</td>',
        '<td>' + CAST(CAST(clmns.precision AS INT) AS VARCHAR(20)) + '</td>',
        '<td>' + CAST(CAST(clmns.scale AS INT) AS VARCHAR(20)) + '</td>',
        '<td>' + CAST(clmns.is_nullable AS VARCHAR(20)) + '</td>' ,
        '<td>' + CAST(clmns.is_computed AS VARCHAR(20)) + '</td>' ,
        '<td>' + CAST(clmns.is_identity AS VARCHAR(20)) + '</td>' ,
        '<td>' + isnull(CAST(cnstr.definition AS VARCHAR(20)),'') + '</td>'
        FROM sys.tables AS tbl INNER JOIN sys.all_columns AS clmns
        ON clmns.object_id=tbl.object_id
        LEFT OUTER JOIN sys.indexes AS idx
        ON idx.object_id = clmns.object_id
        AND 1 =idx.is_primary_key
        LEFT OUTER JOIN sys.index_columns AS idxcol
        ON idxcol.index_id = idx.index_id
        AND idxcol.column_id = clmns.column_id
        AND idxcol.object_id = clmns.object_id
        AND 0 = idxcol.is_included_column
        LEFT OUTER JOIN sys.types AS udt
        ON udt.user_type_id = clmns.user_type_id
        LEFT OUTER JOIN sys.types AS typ
        ON typ.user_type_id = clmns.system_type_id
        AND typ.user_type_id = typ.system_type_id
        LEFT JOIN sys.default_constraints AS cnstr
        ON cnstr.object_id=clmns.default_object_id
        LEFT OUTER JOIN sys.extended_properties exprop
        ON exprop.major_id = clmns.object_id
        AND exprop.minor_id = clmns.column_id
        AND exprop.name = 'MS_Description'
        WHERE (tbl.name = @TableName and
        exprop.class = 1) --I don't wand to include comments on indexes
        ORDER BY clmns.column_id ASC
        PRINT '</tr></table>'
        PRINT '</div>'
        FETCH NEXT FROM Tbls
        INTO @TableName
    END
    PRINT '</body></HTML>'
    CLOSE Tbls
    DEALLOCATE Tbls
    
    
    --方式三
    
    SELECT
    --表名=case when a.colorder=1 then d.name else '' end,
    --表说明=case when a.colorder=1 then isnull(f.value,'') else '' end,
    表名=(case when a.colorder=1 then d.name else '' end),--d.name,
    表说明=(case when a.colorder=1 then isnull(f.value,'') else '' end),--isnull(f.value,'') ,
    字段序号=a.colorder,
    字段名=a.name,
    标识=case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then ''else '' end,
    主键=case when exists(SELECT 1 FROM sysobjects where xtype='PK' 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,''),
    字段说明=isnull(g.[value],'')
    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.major_id and f.minor_id =0
    --where d.name='要查询的表' --如果只查询指定表,加上此条件
    order by d.name,a.id,a.colorder
  • 相关阅读:
    Git 标签管理
    Git 分支管理
    HTTP 400 错误 编译器错误消息: CS0016
    使用DOS指修改文件名
    跨服务器连接数据库
    SharePoint 2013 页面访问,Url中间多一段"_layouts/15/start.aspx#"
    SharePoint 2013 搜索高级配置
    sharepoint 2013 创建母版页
    SharePoint 2013上传AI格式文件,再次下载后变成了PS格式文件
    SharePoint 2013 配置启用搜索服务
  • 原文地址:https://www.cnblogs.com/zyx321/p/10070531.html
Copyright © 2020-2023  润新知