if object_id('dbo.usp_ExtendedProperties') is not null drop procedure dbo.usp_ExtendedProperties go /*================================================================================================== SAMPLE USAGE Get all table-level properties dbo.usp_ExtendedProperties Get all column-level properties for a table dbo.usp_ExtendedProperties 'dbo','mytable' Get all properties for a column dbo.usp_ExtendedProperties 'dbo','mytable','Descrip' Get a specific property for a column dbo.usp_ExtendedProperties 'dbo','mytable','Descrip','MS_Description' Set a property on a table dbo.usp_ExtendedProperties 'dbo','mytable',null,'MS_Description','xyz' Set a property on a column dbo.usp_ExtendedProperties 'dbo','mytable','mycol','MS_Description','xyz' ==================================================================================================*/ create procedure dbo.usp_ExtendedProperties ( @schema sysname = 'dbo' ,@table sysname = null ,@column sysname = null ,@property sysname = null ,@value sql_variant = null ) as /*---------------------------*/ /* declare @schema sysname ,@table sysname ,@column sysname ,@property sysname ,@value sql_variant select @schema = 'dbo' ,@table = 'myTable' */ /*---------------------------*/ /*--- Inits ---*/ declare @level3 sysname /*--- Set Property Value (?) ---*/ if @schema is not null and @table is not null and @property is not null and @value is not null begin set @level3 = case when @column is not null then 'column' else null end /*--- Update Existing Property ---*/ if exists (select 2 from ::fn_listextendedproperty(null,'schema', @schema, 'table', @table, @level3, @column) where name = @property) begin /*--- Update Existing Table Property ---*/ if @column is null execute sp_updateextendedproperty @property, @value, N'SCHEMA', @schema, N'TABLE', @table /*--- Update Existing Column Property ---*/ else execute sp_updateextendedproperty @property, @value, N'SCHEMA', @schema, N'TABLE', @table, N'COLUMN', @column end /* update existing */ /*--- Create New Property ---*/ else begin /*--- Create New Table Property ---*/ if @column is null execute sp_addextendedproperty @property, @value, N'SCHEMA', @schema, N'TABLE', @table /*--- Create New Column Property ---*/ else execute sp_addextendedproperty @property, @value, N'SCHEMA', @schema, N'TABLE', @table, N'COLUMN', @column end /* create new */ end /* set property value */ /*--- Get Properties ---*/ else begin set @level3 = case when @table is not null then 'column' else null end select objtype ,objname ,name -- property name ,value from ::fn_listextendedproperty(null,'schema', @schema, 'table', @table, @level3, @column) end /* get properties */
ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2024, Patrick Jasinski.