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
@objectsysname
,@columnsysname= null
,@propertysysname
,@valuesql_variant
,@actionvarchar(6)= 'save'
as
/*
————————————————————————————————————————————————————————————————————————————————
© 2000-07 • NightOwl Development • All rights reserved.
————————————————————————————————————————————————————————————————————————————————
Purpose :Maintains the sysproperties table by wrapping system procedures:
History :
————————————————————————————————————————————————————————————————————————————————
*/
declare
@idint
,@lvl_0_namesysname
,@lvl_0_typevarchar(128)
,@lvl_1_namesysname
,@lvl_1_typevarchar(128)
,@lvl_2_namesysname
,@lvl_2_typevarchar(128)
,@smallIdsmallint
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
fromdbo.sysobjects
whereid = @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 0then 'column'
when 1then 'parameter'
else @lvl_2_type end
fromdbo.syscolumns
whereid = @id
andname= isnull(@column, '%none%')
--either update or delete if the property exists
if exists (selecttop 1 * from dbo.sysproperties
whereid= @id
andsmallid= @smallid
andname= @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 
