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

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

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

Good Job.

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

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$'.

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

• 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.

Many thanks, great article

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?