sql server导出数据字典 |
您所在的位置：网站首页 › 数据库怎么导出数据字典 › sql server导出数据字典 |
sql server导出数据字典
|
【3.1】打开sql server管理器，给选定的表添加描述信息，给指定的字段添加描述信息。
直接在表上或者字段上右键属性-扩展属性，添加一个key-value对，key就是“MS_Description",value就是你自己的描述。详细见下图： 给字段添加描述的过程与上述一致，不在赘述。 【3.2】执行以下这段sql语句，然后复制查询到的结果到文本，保存为html即可。 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 '' PRINT '' PRINT '' PRINT '数据库字典' PRINT '' 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 '' PRINT '' PRINT '' FETCH NEXT FROM Tbls INTO @TableName WHILE @@FETCH_STATUS = 0 BEGIN PRINT '' Select '表' + @TableName + ' : '+cast(Value as varchar(1000)) + '' FROM sys.extended_properties A WHERE A.major_id = OBJECT_ID(@TableName) and name = 'MS_Description' and minor_id = 0 PRINT '' --Get the Description of the table --Characters 1-250 PRINT '' --Set up the Column Headers for the Table PRINT '字段名称' PRINT '描述' PRINT '主键' PRINT '外键' PRINT '类型' PRINT '长度' PRINT '数值精度' PRINT '小数位数' PRINT '允许为空' PRINT '计算列' PRINT '标识列' PRINT '默认值' --Get the Table Data SELECT '', '' + CAST(clmns.name AS VARCHAR(35)) + '', '' + ISNULL(CAST(exprop.value AS VARCHAR(500)),'') + '', '' + CAST(ISNULL(idxcol.index_column_id, 0)AS VARCHAR(20)) + '', '' + 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)) + '', '' + CAST(udt.name AS CHAR(15)) + '' , '' + 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)) + '', '' + CAST(CAST(clmns.precision AS INT) AS VARCHAR(20)) + '', '' + CAST(CAST(clmns.scale AS INT) AS VARCHAR(20)) + '', '' + CAST(clmns.is_nullable AS VARCHAR(20)) + '' , '' + CAST(clmns.is_computed AS VARCHAR(20)) + '' , '' + CAST(clmns.is_identity AS VARCHAR(20)) + '' , '' + isnull(CAST(cnstr.definition AS VARCHAR(20)),'') + '' 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 '' PRINT '' FETCH NEXT FROM Tbls INTO @TableName END PRINT '' CLOSE Tbls DEALLOCATE Tbls
如下图，第二个框是”以文本方式显示结果”，不要选错了： 【4】获取带目录的html USE [SFKD] go SET ANSI_NULLS ON; GO SET QUOTED_IDENTIFIER ON; GO -- ============================================= -- Description: -- ============================================= SET NOCOUNT ON; /* *输出头部信息 */ PRINT ''; PRINT ''; PRINT ' '; PRINT ' 数据库字典'; PRINT ' '; PRINT ' body{margin:0;cursor:default;font-family: Arial, Helvetica, sans-serif, "微软雅黑" , "宋体";}'; PRINT ' .tableBox{margin:10px auto; padding:0px; width:1200px; height:auto; background:#EAEFF3; border:1px solid #45360A; margin-top:50px;}'; PRINT ' .tableBox h3 {font-size:12pt; height:30px; line-height:30px; background:#460B6A; padding:0px 0px 0px 15px; color:#FFF; margin:0px; text-align:left }'; PRINT ' .tableBox table {width:1200px; padding:0px }'; PRINT ' .tableBox th {height:25px; border-top:1px solid #FFF; border-left:1px solid #FFF; background:#C0DBF7; font-size:14px; }'; PRINT ' .tableBox td {font-size: 12px;height:25px; padding-left:10px; border-top:1px solid #FFF; border-left:1px solid #FFF; }'; PRINT ' .tableBox tr:hover { background:#C0DBF7; } '; PRINT ' .tableBox .field{font-weight:bold;color:#276926;}'; PRINT ' '; PRINT ' '; PRINT ' '; PRINT '' + DB_NAME() + ' 数据库字典生成于：' + CONVERT(VARCHAR, GETDATE(), 120) + ''; /* *数据库菜单列表 */ --数据库表 DECLARE @tableDBTABLE TABLE ( id INT IDENTITY(1, 1) PRIMARY KEY , tableName NVARCHAR(300) ); INSERT INTO @tableDBTABLE ( tableName ) SELECT DISTINCT TABLE_NAME AS tableName FROM INFORMATION_SCHEMA.COLUMNS WHERE ( SELECT COUNT(*) 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 = TABLE_NAME AND exprop.class = 1 ) ) > 0 ORDER BY TABLE_NAME; --输出表目录信息 DECLARE @tname_cur VARCHAR(200); DECLARE @count_t1 INT; DECLARE @i_t1 INT; DECLARE @i_t2 INT; DECLARE @字段名称 VARCHAR(200); DECLARE @类型 VARCHAR(200); DECLARE @长度 VARCHAR(200); DECLARE @数值精度 VARCHAR(200); DECLARE @小数位数 VARCHAR(200); DECLARE @默认值 VARCHAR(200); DECLARE @允许为空 VARCHAR(200); DECLARE @外键 VARCHAR(200); DECLARE @主键 VARCHAR(200); DECLARE @描述 VARCHAR(4000); --初始化值 SET @i_t1 = 1; SET @i_t2 = 1; SELECT @count_t1 = COUNT(*) FROM @tableDBTABLE; IF @count_t1 > 0 BEGIN PRINT ' 目录：'; WHILE @i_t1 0 BEGIN SET @i_t1 = 1; WHILE @i_t1 = 0 BEGIN -- 切库 SET @Sql = N'use ' + '['+@DBname+']' -- 获取数据字典 SET @Sql1 = N' SELECT ( CASE WHEN a.colorder = 1 THEN d.name ELSE '''' END ) TableName, a.colorder, a.name, ( CASE WHEN COLUMNPROPERTY ( a.id, a.name, ''' + 'IsIdentity' + ''' ) = 1 THEN ''' + '√' + ''' ELSE '''' END ) IsIdentity , ( 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 ) AS IsPK, b.name AS Style, a.length AS Bytes, COLUMNPROPERTY ( a.id, a.name, ''' + 'PRECISION' + ''' ) AS Length, ISNULL ( COLUMNPROPERTY ( a.id, a.name, ''' + 'Scale' + ''' ), 0 ) AS decimal , ( CASE WHEN a.isnullable = 1 THEN ''' + '√' + ''' ELSE '''' END ) [IsNull], ISNULL ( e.text, '''' ) AS [Default], ISNULL ( g.[value], '''' ) AS Notes INTO #T1 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 ORDER BY a.id, a.colorder, TableName' -- 获取表上索引的使用状况 SET @Sql2 = N' SELECT DB_NAME ( a.[database_id] ) AS DateBaseName, OBJECT_NAME ( a.[object_id] ) AS TableName, MAX ( a.last_user_update ) AS last_user_update, MAX ( a.[last_user_lookup] ) AS last_user_lookup , MAX ( a.[last_user_scan] ) AS last_user_scan, MAX ( a.[last_user_seek] ) AS last_user_seek INTO #T2 FROM sys.dm_db_index_usage_stats AS a WHERE a.[database_id] = DB_ID () GROUP BY a.[database_id], a.[object_id]' --组合输出 SET @Sql3 = N' INSERT INTO master.dbo.metadata ( [DateBaseName], [TableName], [colorder], [name], [IsIdentity], [IsPK], [Style], [Bytes], [Length], [decimal], [IsNull], [Default], [Notes], [last_user_update] , [last_user_lookup] ,[last_user_scan], [last_user_seek] ) SELECT DateBaseName, #T1.[TableName], [colorder], [name], [IsIdentity], [IsPK], [Style], [Bytes], [Length], [decimal], [IsNull], [Default], [Notes], last_user_update, last_user_lookup , last_user_scan ,last_user_seek FROM #T1 LEFT JOIN #T2 ON #T1.TableName = #T2.TableName AND #T1.colorder = 1' EXEC ( @Sql + @Sql1 + @Sql2 + @Sql3 ) FETCH mycur INTO @DBname END CLOSE mycur DEALLOCATE mycur DROP TABLE #DBName go USE master SELECT ISNULL ( [DateBaseName], '' ) AS '库名', ISNULL ( [TableName], '' ) AS '表名', ISNULL ( [colorder], '' ) AS '字段序号', ISNULL ( [name], '' ) AS '列名', ISNULL ( [IsIdentity], '' ) AS '是否标识' , ISNULL ( [IsPK], '' ) AS '是否主键', ISNULL ( [Style], '' ) AS '类型', ISNULL ( [Bytes], '' ) AS '占空间', ISNULL ( [Length], '' ) AS '长度', ISNULL ( [decimal], '' ) AS '小数位数' , ISNULL ( [IsNull], '' ) AS '是否允许为空', ISNULL ( [Default], '' ) AS '默认值', ISNULL ( [Notes], '' ) AS '注释', [last_user_update] AS '最后更新时间', [last_user_lookup] AS '最后搜索时间' , [last_user_scan] AS '最后扫描时间', [last_user_seek] AS '最后查找时间' FROM [dbo].[MetaData]
|
【本文地址】
今日新闻 |
推荐新闻 |