Script to build a SQL Server Data Dictionary and report with Microsoft Excel

By:   |   Comments (12)   |   Related: > Database Design


Problem

In an earlier tip (Building a SQL Server Data Dictionary with the Extended Properties and Third Party Tools) I outlined the value of using the extended properties across some of the industry leading data modeling tools.  What if I do not have access to any of those tools?  Is it possible to build the extended properties and then be able to generate reports by using Excel or Reporting Services?  How can I capture the data dictionary at a point in time?

Solution

That is a great question.  The easiest way to solve this problem is capture the values from the extended properties into physical table so that you can easily export the data by using Microsoft Excel, Reporting Services or any other reporting tool.

To start the process you need do deploy the store procedure dbo.sp_get_extendedproperty outlined below. 

[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

Second, you need to create a database and table to store the data for reporting purposes.  For this tip, I have created a database called 'DBA' and a table called 'DataDictionary' to store the final data for reporting purposes.

-- 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
)

Third, let's run the stored procedure to populate the table with the data from the "AdventureWorks2008" database for this sample.

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

Now, you can use one of the well known Microsoft SQL Server system store procedures 'sp_MSforeachdb' to populate the table from each of the databases.

TRUNCATE TABLE DBA.dbo.[DataDictionary]
EXEC master.sys.sp_MSforeachdb 'INSERT INTO DBA.dbo.[DataDictionary] exec sp_get_extendedproperty "?"'

Now, if you issue a simple select statement against the [dbo].[DataDictionary] table, you will see results like this:

MSSQLT1

Now, you just need to report on the data from the [dbo].[DataDictionary] table.  Check out the tips below to report on the data from Excel, Reporting Service, Access, etc.

Here is a sample result set in Excel:

MSSQLT2

So, now you have it.  Wasn't that easy to manage?  Here is another reason to use Extended Properties to build a Data Dictionary.

Next Steps
  • Keep in mind how easy it is to build and use the Extended Properties to build a Data Dictionary and leverage third party tools or your own solution.
  • If you build your own solution, you can setup Reporting Services or Excel to extract the data dictionary and share it with users.
  • If you have a lot of servers, I would consider writing an SSIS package to pull the data from all the server into one repository.  In the table used in this tip, that is the reason I included the SQL instance name.
  • If you want to force SQL Server Developer to force to fill out data dictionary, you can also modify the query and write an alert based on missing data dictionary values and send the report to the application owner to update the data dictionary.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Kun Lee Kun Lee is a database administrator and his areas of interest are database administration, architecture, data modeling and development.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Tuesday, May 10, 2016 - 1:50:28 PM - Francisco Araújo Back To Top (41448)

 Thanks  !

Very good!

Francisco

 

 

Wednesday, October 5, 2011 - 8:33:34 AM - Jason Yousef Back To Top (14775)

Great article and script, I know it's intended for DBA use, and thus every DBA will have sa or permission account, but how can you execute it on a remote server, if you don't have permission to create the SP? the only way is to copy the DB locally and run it on it?

 

Tuesday, September 20, 2011 - 10:34:00 AM - Karl Olson Back To Top (14700)

Many thanks, great article

Thursday, August 11, 2011 - 7:00:03 AM - Hugo Durana Back To Top (14370)
Hello there Kun
 
In your next steps, you mention the below bullet point.
  • If you want to force SQL Server Developer to force to fill out data dictionary, you can also modify the query and write an alert based on missing data dictionary values and send the report to the application owner to update the data dictionary.
Since i am a web developer / db developer / front-end... well, a jack-of-all-trades, how could i write such a report?
 
Best regards,
Hugo

Tuesday, February 22, 2011 - 8:13:14 AM - Kun Lee Back To Top (12999)

Hi,

Could you do me a favor? Could you post a script to generate table with extended property that I can simulate the problem? Lost like I need to add blacket but I want to make sure I can double check. I am asking because I have a few tables with special charactors and those are fine so I want to make sure I reproduct correctly first.

thank you,

Kun

Thursday, February 17, 2011 - 4:27:11 AM - Wally Back To Top (12953)

This script doesn't handle all of my SQL Server object names.  It chokes on $, % in object names, which I inherited from a previous database.

 

------------------------------------------------------

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

 

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near '&'.

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near '&'.

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near ''.

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near '_xlnm#Print_Area'.

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near 'FTE$'.

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near 'FTE$'.

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near 'Qs$'.

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near 'Qs$'.

Tuesday, December 9, 2008 - 2:46:40 PM - karunadave Back To Top (2380)

I use an Excel based modelling tool created by Ralph Kimball's group in their Microsoft DW book (available here http://www.rkimball.com/html/booksMDWTtools.html), which makes extensive use of Extended properties for metadata.  11 kinds of Extended property for columns, 6 kinds for tables, for example table metadata extended property 'names' are:

'Display Name'
'Table Description'
'Table Name'
'Table Type'
'Used In Models'
'View Name' 

 

 To surface this in Reporting Services, here are two queries, one for table metadata and one for column metadata.

/*
Query all column metadata in a database
  */
SELECT
  t.name AS [Table Name]
  ,c.name AS [Column Name]
  ,MAX(y.name) as [Data Type]
  ,MAX(c.max_length) AS [Max Length]
  ,MAX(CASE WHEN c.is_nullable = 0                           THEN 'NOT NULL' ELSE 'NULL' END) AS [Is Nullable]
  ,MAX(CASE WHEN ep.name = 'Example Values'                  THEN ep.value ELSE NULL END) AS [Example Values]
  ,MAX(CASE WHEN ep.name = 'Description'                     THEN ep.value ELSE NULL END) AS [Description]
  ,MAX(CASE WHEN ep.name = 'FK To'                           THEN ep.value ELSE NULL END) AS [FK To]
  ,MAX(CASE WHEN ep.name = 'Comments'                        THEN ep.value ELSE NULL END) AS [Comments]
  ,MAX(CASE WHEN ep.name = 'Display Name'                    THEN ep.value ELSE NULL END) AS [Display Name]
  ,MAX(CASE WHEN ep.name = 'Source Table'                    THEN ep.value ELSE NULL END) AS [Source Table]
  ,MAX(CASE WHEN ep.name = 'Source Schema'                   THEN ep.value ELSE NULL END) AS [Source Schema]
  ,MAX(CASE WHEN ep.name = 'ETL Rules'                       THEN ep.value ELSE NULL END) AS [ETL Rules]
  ,MAX(CASE WHEN ep.name = 'SCD  Type'                       THEN ep.value ELSE NULL END) AS [SCD  Type]
  ,MAX(CASE WHEN ep.name = 'Source Field Name'               THEN ep.value ELSE NULL END) AS [Source Field Name]
  ,MAX(CASE WHEN ep.name = 'Source System'                   THEN ep.value ELSE NULL END) AS [Source System]
  ,MAX(CASE WHEN ep.name = 'Extraction Transformation Rules' THEN ep.value ELSE NULL END) AS [Extraction Transformation Rules]
FROM sys.extended_properties AS ep
INNER JOIN sys.tables AS t ON ep.major_id = t.object_id
INNER JOIN sys.columns AS c ON ep.major_id = c.object_id AND ep.minor_id = c.column_id
INNER JOIN sys.types AS y on c.system_type_id = y.system_type_id
WHERE class = 1
  AND minor_id <> 0
GROUP BY
  t.name
  ,c.name
ORDER BY
  t.name
  ,c.name
;
GO

/*
Query all table column extended property metadata in a database
  */
SELECT
  (t.name) AS [Table Name]
  ,MAX(CASE WHEN ep.name = 'Display Name'      THEN ep.value ELSE NULL END) AS [Display Name]
  ,MAX(CASE WHEN ep.name = 'Table Description' THEN ep.value ELSE NULL END) AS [Table Description]
  ,MAX(CASE WHEN ep.name = 'Table Name'        THEN ep.value ELSE NULL END) AS [Table Name]
  ,MAX(CASE WHEN ep.name = 'Table Type'        THEN ep.value ELSE NULL END) AS [Table Type]
  ,MAX(CASE WHEN ep.name = 'Used In Models'    THEN ep.value ELSE NULL END) AS [Used In Models]
  ,MAX(CASE WHEN ep.name = 'View Name'         THEN ep.value ELSE NULL END) AS [View Name]
FROM sys.extended_properties AS ep
INNER JOIN sys.tables AS t ON ep.major_id = t.object_id
WHERE class = 1
  AND minor_id = 0
GROUP BY
  t.name
ORDER BY t.name
  ;
GO

 

Friday, December 5, 2008 - 8:01:25 AM - [email protected] Back To Top (2340)
thank you:)

Thursday, December 4, 2008 - 3:21:20 PM - tosc Back To Top (2334)

Good Job.

Thursday, December 4, 2008 - 2:55:38 PM - [email protected] Back To Top (2333)

That is great. Sorry about late response. Regarding data type and lengh, I was planning to add those but never had chance to add to it. When I get a chance, I will update here.

 Regards,

Kun


Thursday, December 4, 2008 - 1:46:00 AM - CalleM Back To Top (2326)

That was my fault - it runs ok now.

But how can I store additionally type and length of the fields in the DataDictionary table ? 

Regards

 

 

Tuesday, December 2, 2008 - 12:10:09 PM - CalleM Back To Top (2300)

That is great. I just run the scripts. But the command

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

only created 5 rows for my database with about 50 tables and hundreds of fields.
It just took three tables and there 1 or 2 fields.

The scripts all seem to run ok.

What is wrong?

Thanks

Calle

 














get free sql tips
agree to terms