Script to simplify maintenance of sysproperties
This procedure will maintain the sysproperties table by wrapping system procedures:
• sp_addextendedproperty
• sp_dropextendedproperty
• sp_updateextendedproperty
The parameters are:
• @object -- primary name of the object being to be maintained.
• @column -- column or parameter name [optional].
• @property -- name of the property.
• @value -- description associated with the property.
• @action -- save or delete.
Usage : exec sp__SysProperties_sav 'FK_ForeignKey', null, 'MS_Description', 'this is a foreign key', ['save']
-- add a property to a parameter
sp__SysProperties_sav 'my_proc', '@some_param', 'MS_Description', 'this documents a parameter', ['save']
-- removed the property
sp__SysProperties_sav 'my_proc', '@some_param', 'MS_Description', null, 'delete'
I haven't figured out indexes or user types --- yet.
use master
go
create procedure dbo.sp__SysProperties_sav
@object sysname
, @column sysname = null
, @property sysname
, @value sql_variant
, @action varchar(6) = 'save'
as
/*
————————————————————————————————————————————————————————————————————————————————
© 2000-07 • NightOwl Development • All rights reserved.
————————————————————————————————————————————————————————————————————————————————
Purpose : Maintains the sysproperties table by wrapping system procedures:
History :
————————————————————————————————————————————————————————————————————————————————
*/
declare
@id int
, @lvl_0_name sysname
, @lvl_0_type varchar(128)
, @lvl_1_name sysname
, @lvl_1_type varchar(128)
, @lvl_2_name sysname
, @lvl_2_type varchar(128)
, @smallId smallint
set nocount on
set @property = lower(@property) -- done for uniformity
set @id = isnull(object_id(@object), 0)
if @id > 0
begin
-- set the level 0, 1 and possibly 2 name/type values
select @lvl_0_name = user_name(uid)
, @lvl_0_type = 'user'
, @lvl_1_name = case
when xtype = 'd' then isnull(object_name(parent_obj), name)
when xtype in ('c', 'pk', 'f', 'tr', 'uk') then object_name(parent_obj)
else name end
, @lvl_1_type = case xtype
when 'd' then case parent_obj
when 0 then 'default'
else 'table' end
when 'c' then 'table'
when 'pk' then 'table'
when 'f' then 'table'
when 'tr' then 'table'
when 'uk' then 'table'
when 'fn' then 'function'
when 'if' then 'function'
when 'tf' then 'function'
when 'p' then 'procedure'
when 'r' then 'rule'
when 'u' then 'table'
when 'v' then 'view'
else null end
, @lvl_2_name = case
when xtype = 'd' then object_name(parent_obj)
when xtype in ('c', 'pk', 'f', 'tr', 'uk') then name
else null end
, @lvl_2_type = case xtype
when 'c' then 'constraint'
when 'd' then case parent_obj when 0 then null else 'default' end
when 'pk' then 'constraint'
when 'f' then 'constraint'
when 'tr' then 'trigger'
when 'uk' then 'constraint'
else null end
from dbo.sysobjects
where id = @id
set @column = nullif(rtrim(@column), '')
set @smallid = 0
-- retrieve the column/parameter id if provided
select @smallid = colid
, @lvl_2_name = isnull(@lvl_2_name, name)
, @lvl_2_type = case charindex('@', name)
when 0 then 'column'
when 1 then 'parameter'
else @lvl_2_type end
from dbo.syscolumns
where id = @id
and name = isnull(@column, '%none%')
-- either update or delete if the property exists
if exists ( select top 1 * from dbo.sysproperties
where id = @id
and smallid = @smallid
and name = @property )
begin
if @action = 'delete'
begin
exec dbo.sp_dropextendedproperty @property
, @lvl_0_type
, @lvl_0_name
, @lvl_1_type
, @lvl_1_name
, @lvl_2_type
, @lvl_2_name
end
else
begin
exec dbo.sp_updateextendedproperty @property
, @value
, @lvl_0_type
, @lvl_0_name
, @lvl_1_type
, @lvl_1_name
, @lvl_2_type
, @lvl_2_name
end
end
-- if the property doesn't exist add it if you're not deleting
else if @action != 'delete'
begin
exec dbo.sp_addextendedproperty @property
, @value
, @lvl_0_type
, @lvl_0_name
, @lvl_1_type
, @lvl_1_name
, @lvl_2_type
, @lvl_2_name
end
end
return @@error
go
