Data Dictionary Part II
Ok, after putting up my first post on the Data Dictionary I had posters remorse. There is a simple change that makes the script shown in the previous post a little more user friendly. First the view metadata.columnsDataDictionary need to be changed from a SQL Select using a pivot to just a self join from the metadata.columnsExtendedProperties to allow for columns that shared the same domain to easily display different MS_Description values. The second change was to allow the procedure metadata.columnsExtendedPropertiesRepopulate to allow for an overall overwrite or just to add new values and retain old MS_Description extended propertie values. I don't think I mentioned this earlier the extended property of 'MS_Description' will be picked up by a number of development applications so it is useful to stick your data dictionary value in there but not to overwrite one if it came supplied with say a third part application installation or if you are allowing a third party applicaiton manage your descriptioins in the database.
Well thats it here is the code with a simple example:
USE [steves]
GO
IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[metadata].[columnsExtendedProperties]'))
DROP VIEW [metadata].[columnsExtendedProperties]
GO
IF EXISTS (SELECT * FROM sys.views
WHERE object_id = OBJECT_ID(N'[metadata].[columnsDataDictionary]'))
DROP VIEW [metadata].[columnsDataDictionary]
GO
IF EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[metadata].[dataDictionary]') AND type in (N'U'))
DROP TABLE [metadata].[dataDictionary]
GO
IF EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[testExtendedProperties]') AND type in (N'U'))
DROP TABLE [dbo].[testExtendedProperties]
GO
IF EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[metadata].[columnsExtendedPropertiesRepopulate]') AND type in (N'P', N'PC'))
DROP PROCEDURE [metadata].[columnsExtendedPropertiesRepopulate]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [metadata].[dataDictionary](
[domainName] [varchar](50) NOT NULL,
[domainDesc] [varchar](1000) NOT NULL,
PRIMARY KEY CLUSTERED
(
[domainName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF
, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
go
exec sp_addextendedproperty 'domain', 'domainName'
, 'schema', 'metadata', 'table', 'dataDictionary', 'column', 'domainName'
exec sp_addextendedproperty 'domain', 'domainDesc'
, 'schema', 'metadata', 'table', 'dataDictionary', 'column', 'domainDesc'
insert into metadata.dataDictionary
(domainName,domainDesc)
values('domainName','Short text name for the classification of a businessdata type or attribute')
insert into metadata.dataDictionary
(domainName,domainDesc)
values('domainDesc','Description of the a business data type or attribute')
GO
create view [metadata].[columnsExtendedProperties]
as
----------------------------------------------
--Steve Fibich
--1.11.2007
--This view is used to match columns to their extended
--properties. This is an extension of sys.extended_properties
--system view.
--
----------------------------------------------
select
top 100 percent
s.name as schemaName
,t.name as TableName
,c.name as columnName
,e.name as EName
,e.value as EDesc
,c.column_id as columnID
from
sys.extended_properties e
inner join
sys.tables t
on
t.object_id=e.major_id
inner join
sys.columns c
on
e.minor_id=c.column_id
and
e.major_id=c.object_id
inner join
sys.schemas s
on
t.schema_id=s.schema_id
where
class=1
order by
s.name
,t.name
,c.column_id
,e.name
GO
create view [metadata].[columnsDataDictionary]
as
----------------------------------------------
--Steve Fibich
--1.11.2007
--This view is used to match columns to their extended
--properties. It does this for particular extended properties
--"domain" and "MS_Description. It then pivots these results for a
--more useful layout.
--
----------------------------------------------
select
top 100 percent
cep1.schemaName
,cep1.tableName
,cep1.columnName
,cep1.eDesc as domain
,cep2.eDesc as MS_Description
from
(
select
schemaName
,tableName
,columnName
,eDesc
,eName
,columnId
from [metadata].[columnsExtendedProperties]
where
EName='domain'
) cep1
full outer join
(
select
schemaName
,tableName
,columnName
,eDesc
,eName
,columnId
from
[metadata].[columnsExtendedProperties]
where
EName='MS_Description'
) cep2
on
cep1.schemaName=cep2.schemaName
and
cep1.tableName=cep2.tableName
and
cep1.columnID=cep2.columnID
order by
cep1.schemaName
,cep1.tableName
,cep1.columnID
GO
go
create proc metadata.columnsExtendedPropertiesRepopulate
----------------------------------------------
--Steve Fibich
--1.11.2007
--This proc removes any 'MS_Description' extended properties
--from any columns of any tables that have matching entires in the
--metadata.datadictionary table and that have an extended propertie of 'domain'
--This is to try to make keeping 'MS_Description' descripions upto date eaiser
--
--@overwrite=0 turns off overwriting so existing MS_Descriptions will be left alone
--@overwrite=1 existing MS_Description values will be deleted and re-created
--@debug =0 sets additional logging off
--@debug=1 sets additional looging on
--
----------------------------------------------
@overwrite bit=0
,@debug bit=0
as
--if @debug=0
-- BEGIN
-- select @debug=debug from metadata.logging where schemaName='metadata'
-- END
declare @schemaName sysname
declare @tableName sysname
declare @columnName sysname
declare @domainDesc varchar(1000)
declare extProp_cursor cursor for
select
e.schemaName
,e.tableName
,e.columnName
,dd.domainDesc
from
metadata.dataDictionary dd
inner join
[metadata].[columnsExtendedProperties] e
on
dd.domainName=edesc
where
ename='domain'
open extProp_cursor
Fetch next from extProp_cursor into
@schemaName,@tableName,@columnName,@domainDesc
while @@fetch_status=0
BEGIN
if @debug=1
BEGIN
select @schemaName,@tableName,@columnName,@domainDesc
END
if (exists (select * from ::fn_listextendedproperty('MS_Description', 'schema'
, @schemaName, 'table', @tableName, 'column', @columnName)) and (@overwrite=0))
BEGIN
execute sp_dropextendedproperty 'MS_Description','schema'
,@schemaName,'table',@tableName,'column',@columnName
END
if not exists (select * from ::fn_listextendedproperty('MS_Description', 'schema'
, @schemaName, 'table', @tableName, 'column', @columnName))
BEGIN
execute sp_addextendedproperty 'MS_Description',@domainDesc,'schema'
,@schemaName,'table',@tableName,'column',@columnName
END
Fetch next from extProp_cursor into
@schemaName,@tableName,@columnName,@domainDesc
END
close extProp_cursor
deallocate extProp_cursor
-----------------------------------------
--Example Table and Extended Properties--
-----------------------------------------
go
create table dbo.testExtendedProperties
(
column1 int
,column2 int
,column3 int
,column4 int
,column5 int
,column6 int
)
exec sp_addextendedproperty 'domain', 'domainName'
, 'schema', 'dbo', 'table', 'testExtendedProperties', 'column', 'column1'
exec sp_addextendedproperty 'domain', 'domainDesc'
, 'schema', 'dbo', 'table', 'testExtendedProperties', 'column', 'column2'
exec sp_addextendedproperty 'domain', '3rdDomainDesc'
, 'schema', 'dbo', 'table', 'testExtendedProperties', 'column', 'column3'
exec sp_addextendedproperty 'domain', '4thDomainDesc'
, 'schema', 'dbo', 'table', 'testExtendedProperties', 'column', 'column4'
exec sp_addextendedproperty 'domain', '3rdDomainDesc'
, 'schema', 'dbo', 'table', 'testExtendedProperties', 'column', 'column5'
exec sp_addextendedproperty 'domain', '4thDomainDesc'
, 'schema', 'dbo', 'table', 'testExtendedProperties', 'column', 'column6'
insert into metadata.dataDictionary
(domainName,domainDesc)
values('3rdDomainDesc','This is the 3rd domain description that we have it should be on column3 and column5')
insert into metadata.dataDictionary
(domainName,domainDesc)
values('4thDomainDesc','This is the 4th domain description that we have it should be on column4 and column6')
execute metadata.columnsExtendedPropertiesRepopulate
select * from [metadata].[columnsDataDictionary]
select * from [metadata].[columnsExtendedProperties]