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

这是一个很好的问题。解决这个问题最简单的方法是从扩展属性中获得值到物理表中，因此你可以很容易通过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