自己动手制作数据字典金沙官网线上

 

--数据库表名及字段名都无数据字典,现在补充上。

 

--暂时想到如下笨办法:

--思路,分两步,一是填写表的字典。二是填写表中字段的字典。

--将表名导出到Table_Description

--将列名导出到Column_Description

--然后填写上Description,最后调用存储过程,分别更新或添加字典到表和字段上。

--最后,利用三视图,显示数据字典:表结构,索引,全部用户对象。表结果是重点。

--也可以用动软生成Word或Html形式。

 

一、开始:导出表信息到Table_Description表

创建Table_Description表

create table Table_Description

(TableName nvarchar(50),Descriptions nvarchar(255),RowCounts int,IsUsed bit)  

载入用户表到Table_Description表

insert into Table_Description (TableName)

select name from sys.tables a where name not in ('sysdiagrams','Table_Description','Column_Description')

and not exists (select 1 from Table_Description where TableName=a.name)

order by name

 

统计表记录行总数代码示例:

select schema_name(t.schema_id) as [Schema], t.name as TableName,i.rows as [RowCount]

from sys.tables as t, sysindexes as i

where t.object_id = i.id and i.indid <=1

 

更新表记录行总数

update  a set rowcounts=b.[rowcount] from Table_Description  a,

(select schema_name(t.schema_id) as [Schema], t.name as TableName,i.rows as [RowCount]

from sys.tables as t, sysindexes as i

where t.object_id = i.id and i.indid <=1) b

where a.TableName=b.TableName

 

单独填写各表的字典

update Table_Description set Descriptions='客户资料表.主档',isused=1 where TableName='KFZL' 

 

删除无用资料字典表

delete from Table_Description where isnull(IsUsed,0)=0

 

到此字典表Table_Description填写完毕,结果如下图:

 

 金沙官网线上 1

 

 二、载入字典表的全部列到Column_Description表

载入字典表的全部列

create table Column_Description

(TableName nvarchar(50),ColumnName nvarchar(50),Descriptions nvarchar(255),IsUsed bit)

insert into Column_Description (TableName,ColumnName)

select b.TableName,a.name from sys.all_columns a,Table_Description b

where a.object_id = object_id(b.TableName) and b.IsUsed=1

and not exists (select 1 from Column_Description c where b.TableName=c.TableName and a.name=c.ColumnName)

order by b.TableName,a.column_id

 

 

导出Column_Description到Excel进行字典填写,填写完后,回传到Column_Description表中。效果图如下:

 

 金沙官网线上 2

金沙官网线上 3

 

三、批量添加数据表的字典描述

 

 

declare @TableName nvarchar(50),@Descriptions nvarchar(255)

 

declare cur cursor for

select tablename,descriptions from table_description where isnull(descriptions,'')<>'' order by tablename

open cur

fetch next from cur into @TableName,@Descriptions

while (@@fetch_status=0)

begin

    IF EXISTS (select 1 from sys.extended_properties WHERE name='MS_Description' and major_id=OBJECT_ID(''+@TableName+'') and minor_id=0) 

    begin

        EXEC sp_updateextendedproperty N'MS_Description',@Descriptions,N'SCHEMA',N'dbo',N'TABLE',@TableName

    end

    else

    begin

        EXEC sp_addextendedproperty N'MS_Description',@Descriptions,N'SCHEMA',N'dbo',N'TABLE',@TableName 

    end

    

    print '数据表['+@TableName+']描述['+@Descriptions+']添加成功!'

 

    fetch next from cur into @TableName,@Descriptions

end

close cur

deallocate cur

 

 

四、批量添加数据字段的字典描述

 

 

declare @TableName nvarchar(50),@ColumnName nvarchar(50),@Descriptions nvarchar(255)

 

declare cur cursor for

select tablename,columnname,descriptions from column_description where isnull(descriptions,'')<>'' order by tablename,columnname

open cur

fetch next from cur into @TableName,@ColumnName,@Descriptions

while (@@fetch_status=0)

begin

    IF EXISTS (select 1 from sys.extended_properties WHERE name='MS_Description' and major_id=OBJECT_ID(''+@TableName+'') 

    and minor_id in (select colid from syscolumns where id=object_id(''+@TableName+'') and name=''+@ColumnName+'') )

    begin

        EXEC sp_updateextendedproperty N'MS_Description',@Descriptions,N'SCHEMA',N'dbo',N'TABLE',@TableName,N'COLUMN',@ColumnName

    end

    else

    begin

        EXEC sp_addextendedproperty N'MS_Description',@Descriptions,N'SCHEMA',N'dbo',N'TABLE',@TableName,N'COLUMN',@ColumnName

    end

    

    print '数据表['+@TableName+']字段['+@ColumnName+']描述['+@Descriptions+']添加成功!'

 

    fetch next from cur into @TableName,@ColumnName,@Descriptions

end

close cur

deallocate cur

 

 

 

五、利用视图显示数据字典

 

 

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  */

   --上面写没有排除视图,修改为下面

    --select name from dbo.SysObjects WHERE OBJECTPROPERTY(ID, 'IsUserTable') = 1

    --and name<>'sysdiagrams' order by name

    select tablename from table_description order by tablename

 

OPEN Tbls  

PRINT '<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"&gt;'  

PRINT '<html xmlns="http://www.w3.org/1999/xhtml"&gt;'  

PRINT '<head>'

PRINT '<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />'

PRINT '<title>'+db_name()+'数据库字典</title>'  

PRINT '<style type="text/css">'  

PRINT 'body{margin:0; font:11pt "arial", "微软雅黑"; cursor:default;}'  

PRINT '.tableBox{margin:10px auto; padding:0px; width: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 {width: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    

 

 

 

 

效果图如下:

 

 金沙官网线上 4

 

 

 总结:

 

 整体来说,自己第一次动手还算满意!

 

 

 

 

 

 

 

本文由金沙官网线上发布于数据库,转载请注明出处:自己动手制作数据字典金沙官网线上

您可能还会对下面的文章感兴趣: