用Excel建立SQL Server数据字典和报表的脚本

核心提示：这是一个很好的问题，解决这个问题最简单的方法是从扩展属性中获得值到物理表中，用Excel建立SQL Server数据字典和报表的脚本，因此你可以很容易通过Microsoft Excel、报表服务或其它任何报表工具导出数据， 要启动这个过程，我已经创建了一个叫'DBA'的数据库和'DataDict

这是一个很好的问题。解决这个问题最简单的方法是从扩展属性中获得值到物理表中，因此你可以很容易通过Microsoft Excel、报表服务或其它任何报表工具导出数据。

要启动这个过程，你需要配置下面列出的存储过程dbo.sp_get_extendedproperty。

　　 [dbo].[sp_get_extendedproperty]
　　USE [master]
　　GO
　　CREATE PROCEDURE [dbo].[sp_get_extendedproperty]
　　@databasename varchar(128) = NULL
　　as
　　BEGIN
　　SET NOCOUNT ON
　　SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
　　IF @databasename IS NULL
　　SET @databasename = db_name()
　　DECLARE @sqltext nvarchar(4000)
　　IF object_id(N'tempdb.dbo.##temp___DataDictionary') IS NOT NULL
　　DROP TABLE ##temp___DataDictionary
　　IF object_id(N'tempdb.dbo.##temp___DataDictionary_schema') IS NOT NULL
　　DROP TABLE ##temp___DataDictionary_schema
　　CREATE TABLE ##temp___DataDictionary(
　　[tableschema] varchar(128) NULL,
　　[tablename] varchar(128) NULL,
　　[columnname] varchar(128) NULL,
　　[xtype] varchar(8) NULL,
　　[description] nvarchar(4000) NULL
　　)
　　CREATE TABLE ##temp___DataDictionary_schema(
　　[tableschema] varchar(128),
　　[tablename] varchar(128) NULL,
　　)
　　-- Deploy Database Property
　　SET @sqltext = 'INSERT INTO ##temp___DataDictionary ([description], [xtype]) SELECT cast(value as nvarchar(4000)), ''D'' FROM '
　　+ @databasename + '.sys.fn_listextendedproperty(default, default, default, default, default, default, default)'
　　EXECUTE (@sqltext)
　　-- Get table level data dictionary
　　SET @sqltext = 'INSERT INTO ##temp___DataDictionary_schema SELECT DISTINCT TABLE_SCHEMA, TABLE_NAME FROM '
　　+ @databasename + '.INFORMATION_SCHEMA.TABLES'
　　EXECUTE(@sqltext)
　　DECLARE table_cursor CURSOR FOR
　　SELECT DISTINCT [tableschema] FROM ##temp___DataDictionary_schema
　　DECLARE @TABLE_SCHEMA VARCHAR(128)
　　OPEN table_cursor
　　FETCH NEXT FROM table_cursor INTO @TABLE_SCHEMA
　　WHILE @@FETCH_STATUS = 0
　　BEGIN
　　SET @sqltext = 'INSERT INTO ##temp___DataDictionary ([tableschema], [tablename],[description], [xtype]) SELECT '
　　+ '''' + @TABLE_SCHEMA + '''' + + ', objname, cast(value as nvarchar(4000)), ''U'' FROM '
　　+ @databasename + '.sys.fn_listextendedproperty (NULL, ''schema'', '
　　+ '''' + @TABLE_SCHEMA + ''''+ ', ''table'', default, NULL, NULL)'
　　EXECUTE(@sqltext)
　　FETCH NEXT FROM table_cursor INTO @TABLE_SCHEMA
　　END
　　CLOSE table_cursor
　　DEALLOCATE table_cursor
　　CREATE TABLE ##temp___DataDictionary_keys(
　　[tableschema] varchar(128) NULL,
　　[tablename] varchar(128) NULL,
　　[columnname] varchar(128) NULL,
　　[xtype] varchar(8) NULL
　　)
　　-- Populate all the key types
　　SET @sqltext = 'INSERT INTO ##temp___DataDictionary_keys SELECT U.TABLE_SCHEMA,U.TABLE_NAME, U.COLUMN_NAME, xtype '
　　+ 'FROM ' + @databasename + '.INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE U '
　　+ 'JOIN ' + @databasename + '.sys.sysobjects O ON U.CONSTRAINT_NAME = O.name WHERE O.xtype in (''F'',''PK'')'
　　EXECUTE(@sqltext)
　　-- Get column level
　　DECLARE @TABLE_NAME varchar(128)
　　DECLARE column_cursor CURSOR FAST_FORWARD FOR
　　SELECT [tableschema], [tablename] FROM ##temp___DataDictionary_schema
　　OPEN column_cursor
　　FETCH NEXT FROM column_cursor INTO @TABLE_SCHEMA, @TABLE_NAME
　　WHILE @@FETCH_STATUS = 0
　　BEGIN
　　-- display all columns under MyTable
　　SET @sqltext = 'INSERT INTO ##temp___DataDictionary ([tableschema], [tablename],[columnname], [description]) SELECT '
　　+ '''' + @TABLE_SCHEMA + '''' + ','
　　+ '''' + @TABLE_NAME + '''' + ', objname, cast(value as nvarchar(4000))'
　　+ ' FROM ' + @databasename + '.sys.fn_listextendedproperty (NULL, ''schema'', '
　　+ '''' + @TABLE_SCHEMA + '''' + ', ''table'', '
　　+ '''' + @TABLE_NAME + '''' + ', ''column'', default)'
　　EXECUTE(@sqltext)
　　FETCH NEXT FROM column_cursor INTO @TABLE_SCHEMA, @TABLE_NAME
　　END
　　CLOSE column_cursor
　　DEALLOCATE column_cursor
　　UPDATE D
　　SET D.[xtype] = K.[xtype]
　　FROM ##temp___DataDictionary D JOIN ##temp___DataDictionary_keys K
　　ON D.tableschema = K.tableschema AND D.tablename = K.tablename AND D.columnname = K.columnname
　　SELECT @@servername as servername,@databasename as dbname,tableschema,tablename,columnname,[xtype],[description]
　　FROM ##temp___DataDictionary
　　ORDER BY @@servername ,dbname,tableschema,tablename,columnname,[xtype] asc
　　DROP TABLE ##temp___DataDictionary
　　DROP TABLE ##temp___DataDictionary_keys
　　END

其次，你需要创建一个数据库和表来存储数据已达到报表的目的。在这个技巧中，我已经创建了一个叫'DBA'的数据库和'DataDictionary'的表来存储最后的数据以备报表之需。 　　　　　n Create DBA Database
　　CREATE DATABASE DBA
　　GO
　　USE DBA
　　-- Create Table
　　CREATE TABLE [dbo].[DataDictionary](
　　[sqlinstance] [varchar](128) NOT NULL,
　　[databasename] [varchar](128) NOT NULL,
　　[tableschema] [varchar](128) NULL,
　　[tablename] [varchar](128) NULL,
　　[columnname] [varchar](128) NULL,
　　[xtype] [varchar](8) NULL,
　　[description] [nvarchar](4000) NULL
　　)

第三，让我们运行这个存储过程把"AdventureWorks2008"数据库中的数据导入到这张表中。

INSERT INTO DBA.dbo.[DataDictionary] exec sp_get_extendedproperty 'AdventureWorks2008'