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]