Towards the self-documenting database: extended properties

William Brewer

7 Jan 20077 min read

79.9K

193

With SQL Server, it is strange how some of the most radical improvements that have been introduced over the years have been slipped in almost unnoticed.

This article is in the Product Showcase section for our sponsors at CodeProject. These articles are intended to provide you with information on products and services that we consider useful and of value to developers.

selfdocdatabase_codedownload.zip
- CODE DOWNLOAD
  - spDBDoc.sql

create procedure spDBDoc 
@hierarchy varchar(40)='' ,
@object varchar(40)='',
@value sql_variant=null, --'This column tells you the date of insertion',
@Function varchar(20)='assign'
/*
--lists all extended properties and values for columns of the 'customer' table.
spdbDoc 'user.table.column','dbo.customer'

spDbDoc '','','','possible' --list out all possible hierarchies supporting EPs
--or
spDbDoc @Function='possible'--list out all possible hierarchies supporting EPs

--lists all extended properties and values for columns of the 'customer' table.
spdbDoc 'schema.table.column','dbo.customer'--error on SQL Server 2000
spdbDoc 'user.table.column','dbo.customer'--works on SQL Server 2000

--either alter or create an entry for the MS_Documentation property for insertionDate
spdbDoc  'user.table.column','dbo.customer.insertionDate','This logs the date that the row was inserted'

--either alter or create an entry for the ConvertStyle property for insertionDate
spdbDoc  'user.table.column','dbo.customer.insertionDate.ConvertStyle',113

--either alter or create an entry for the MS_Documentation property for title
spdbDoc  'user.table.column','dbo.customer.title','e.g. Mr Mrs, Madame, sir, etc'

--either alter or create an entry for the 'Range' property for DOB
spdbDoc  'user.table.column','dbo.customer.DOB.range','01 Jan 1920'

spDBDoc 'user.procedure','dbo.spGetCustomer',
	'This procedure gets the customer record corresponding with the customer_ID'

spDBDoc 'user.procedure.parameter','dbo.spGetCustomer.@customer_ID',
	'This is the ID of the customer'

--create a description for the database
spdbDoc  '','','This is a sample database that illustrates how extended properties can be assigned to objects'
--add a new property called 'revision date'
spdbDoc  '','revisionDate','20 Nov 2006'
--append to the value string in the property 'revision date'
spdbDoc  '','revisionDate','-Fixed warning message (21 Nov 2006)','append'
*/
as
Declare @Hits int
Declare @Level0 varchar(40)
Declare @Level1 varchar(40)
Declare @Level2 varchar(40)
Declare @Name0 varchar(40)
Declare @Name1 varchar(40)
Declare @Name2 varchar(40)
Declare @ErrorMessage varchar(40)
Declare @V8 int
Declare @V9 int
Declare @Split int
Declare @Split2 int
Declare @Split3 int
Declare @Property Varchar(80)
Declare @PropertyForAmendment Varchar(80)
Declare @SQLVersion char(1)
Set nocount on
Declare @hierarchies table (Hierarchy varchar(40),Level0 varchar(40),Level1 varchar(40),Level2 varchar(40), V8 int, V9 int)
insert into @hierarchies Select '',null,null,null,1,1--database 
insert into @hierarchies Select 'user.aggregate','user','aggregate',null,0,1
insert into @hierarchies Select 'schema.aggregate','schema','aggregate',null,0,1
insert into @hierarchies Select 'contract','Contract',null,null,0,1--service broker
insert into @hierarchies Select 'assembly','assembly',null,null,0,1
insert into @hierarchies Select 'schema.Default','schema','Default',null,0,1
insert into @hierarchies Select 'user.Default','user','default',null,0,1--backward compatibility only
insert into @hierarchies Select 'Event Notification','Event Notification',null,null,0,1
insert into @hierarchies Select 'fileGroup.Logical file Name','fileGroup','Logical file Name',null,0,1
insert into @hierarchies Select 'schema.function','Schema','Function',null,0,1
insert into @hierarchies Select 'user.function','User','Function',null,1,1
insert into @hierarchies Select 'schema.function.column','Schema','Function','Column',0,1
insert into @hierarchies Select 'schema.function.Constraint','Schema','Function','Constraint',0,1
insert into @hierarchies Select 'schema.function.Parameter','Schema','Function','Parameter',0,1
insert into @hierarchies Select 'user.function.column','User','Function','Column',1,1
insert into @hierarchies Select 'user.function.Constraint','User','Function','Constraint',1,1
insert into @hierarchies Select 'user.function.Parameter','User','Function','Parameter',1,1
insert into @hierarchies Select 'Message type','Message type',null,null,0,1
insert into @hierarchies Select 'partition Function','partition Function',null,null,0,1
insert into @hierarchies Select 'partition Scheme','partition Scheme',null,null,0,1
insert into @hierarchies Select 'schema.Procedure','schema','Procedure',null,0,1
insert into @hierarchies Select 'user.Procedure','user','Procedure',null,1,1
insert into @hierarchies Select 'schema.Procedure.Parameter','schema','Procedure','Parameter',0,1
insert into @hierarchies Select 'user.Procedure.Parameter','user','Procedure','Parameter',1,1
insert into @hierarchies Select 'schema.Queue','schema','Queue',null,0,1 --Service Broker
insert into @hierarchies Select 'user.Queue','user','Queue',null,0,1 --Service Broker
insert into @hierarchies Select 'schema.Queue.Event Notification','schema','Queue','Event Notification',0,1--Service Broker
insert into @hierarchies Select 'user.Queue.Event Notification','user','Queue','Event Notification',0,1--Service Broker
insert into @hierarchies Select 'Remote Service Binding','Remote Service Binding',null,null,0,1 --Service Broker
insert into @hierarchies Select 'route','route',null,null,0,1 --Service Broker
insert into @hierarchies Select 'User.Rule','User.Rule',null,null,1,1
insert into @hierarchies Select 'schema.Rule','schema.Rule',null,null,0,1
insert into @hierarchies Select 'user','user',null,null,1,1
insert into @hierarchies Select 'user.Default','user','Default',null,1,0
insert into @hierarchies Select 'Schema','Schema',null,null,0,1
insert into @hierarchies Select 'Service','Service',null,null,0,1
insert into @hierarchies Select 'user.Service','user','Service',null,0,1
insert into @hierarchies Select 'Schema.Service','Schema','Service',null,0,1
insert into @hierarchies Select 'user.Synonym','user','Synonym',null,0,1
insert into @hierarchies Select 'Schema.Synonym','Schema','Synonym',null,0,1
insert into @hierarchies Select 'User.Table','User','Table',null,1,1
insert into @hierarchies Select 'Schema.Table','Schema','Table',null,0,1
insert into @hierarchies Select 'User.Table.Column','User','Table','Column',1,1
insert into @hierarchies Select 'Schema.Table.Column','Schema','Table','Column',0,1
insert into @hierarchies Select 'User.Table.Constraint','User','Table','Constraint',1,1
insert into @hierarchies Select 'Schema.Table.Constraint','Schema','Table','Constraint',0,1
insert into @hierarchies Select 'User.Table.Index','User','Table','Index',1,1
insert into @hierarchies Select 'Schema.Table.Index','Schema','Table','Index',0,1
insert into @hierarchies Select 'User.Table.Trigger','User','Table','Trigger',1,1
insert into @hierarchies Select 'Schema.Table.Trigger','Schema','Table','Trigger',0,1
insert into @hierarchies Select 'trigger','trigger',null,null,0,1--DDL Triggers only (alters any trigger on the database)
insert into @hierarchies Select 'type','type',null,null,0,1--backward compatibility only
insert into @hierarchies Select 'user.Type','user','Type',null,0,1
insert into @hierarchies Select 'Schema.Type','Schema','Type',null,0,1
insert into @hierarchies Select 'User.View','User','View',null,1,1
insert into @hierarchies Select 'Schema.View','Schema','View',null,0,1
insert into @hierarchies Select 'User.View.column','User','View','column',1,1
insert into @hierarchies Select 'Schema.View.column','Schema','View','column',0,1
insert into @hierarchies Select 'User.View.index','User','View','index',1,1
insert into @hierarchies Select 'Schema.View.index','Schema','View','index',0,1
insert into @hierarchies Select 'User.View.Trigger','User','View','Trigger',1,1
insert into @hierarchies Select 'Schema.View.Trigger','Schema','View','Trigger',0,1
insert into @hierarchies Select 'User.XML Schema Collection','User','XML Schema Collection',null,0,1
insert into @hierarchies Select 'Schema.XML Schema Collection','Schema','XML Schema Collection',null,0,1
--what version of SQL Server are we
Select @SQLVersion=convert(char(1),convert(varchar(10),@@microsoftVersion/16))
if @SQLVersion not in ('8','9')
	begin
	raiserror ('sorry but spDBDoc is not written for SQL Server %s',16,1, @SQLVersion)
	return 1
	end
if @Function='possible'
	begin
	if @SQLVersion='8'
		select Hierarchy from @Hierarchies where V8<>0 order by level1,level0
	else
		select Hierarchy from @Hierarchies where V9<>0 order by level1,level0
	return 0
	end
Select @Level0=level0, @Level1=level1, @Level2=level2,@V8=V8, @V9=v9
	from @Hierarchies where Hierarchy like @Hierarchy
Select @hits=@@rowcount
if @hits <>1
	begin
	select @ErrorMessage= case when @hits>1 then 'Ambiguous' else 'Unknown' end
	raiserror ('%s hierarchy ''%s''',16,1,@ErrorMessage,@Hierarchy)
	return 1
	end
if (@SQLVersion='8' and @V8=0) or (@SQLVersion='9' and @V9=0)
	begin
	raiserror ('sorry but %s hierarchy not in SQL Server %s',16,1,@Hierarchy, @SQLVersion)
	return 1
	end

Select @split=charindex('.',@Object+'.',1)
Select @split2=charindex('.',@Object+'....',@Split+1)
Select @split3=charindex('.',@Object+'....',@Split2+1)
--Select @Name0=
select @Property = case when len (@object)>0 then left(@object,@split-1)else null end
if @level0 is not null select @Name0=@Property, @property=null
select @Property = 
	case when @Split between 1 and len(@object) 
	then substring(@object,@Split+1,@split2-@Split-1) 
	else @Property end
if @level1 is not null select @Name1=@Property, @property=null
select @Property = 
	case when @Split2 between @Split and len(@object) 
	then substring(@object,@split2+1,@split3-@Split2-1)
	else @Property end	
if @level2 is not null select @Name2=@Property, @property=null
select @Property = 
	case when @Split3 between @Split2 and len(@object) 
	then substring(@object,@Split3+1,2000)
	else @Property end	
--select @Property
declare @existing table (value sql_variant)
Select @PropertyForAmendment=coalesce(@Property,'MS_Description')
insert into @Existing (Value) 
select Value  from ::fn_listExtendedProperty
	( @PropertyForAmendment,@Level0, @name0, @Level1, @name1, @level2, @name2)
if @Value is not null
	if exists (select 1 from @Existing)
		begin
		if (select count(*) from @existing)>1 
			begin
			raiserror ('Ambiguous property spec',16,1)
			return 1
			end
		if @Function='append' select @value=
			(select 
				cast(value as varchar(8000)) from @Existing)
				+cast(@Value as varchar(8000))
		execute sp_UpdateExtendedProperty @PropertyForAmendment,
			@Value, @Level0, @name0, @Level1, @name1, @level2, @name2
		end
	else
		begin
		execute sp_AddExtendedProperty @PropertyForAmendment,
			@Value, @Level0, @name0, @Level1, @name1, @level2, @name2
		end
select  objname,
	[name], 
	value
  	from ::fn_listExtendedProperty
		( @Property,@Level0, @name0, @Level1, @name1, @level2, @name2)

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Written By

William Brewer

United States

William Brewer is a SQL Server developer who has worked as a Database consultant and Business Analyst for several Financial Services organisations in the City of London.

Towards the self-documenting database: extended properties

License

Comments and Discussions