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
