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)