|
|
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]
Anonymous comments are disabled
|
|
|