Page History: Extended Properties Stored Procedure - SQL Server

Compare Page Revisions

0 Current 0

« Older Revision - Back to Page History - Current Revision

Page Revision: Mon, Nov 21, 2011, 1:13 PM

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','tblCategory'                                              
                                                                                                    
    Get all properties for a column                                                                 
        dbo.usp_ExtendedProperties 'dbo','tblCategory','Descrip'                                    
                                                                                                    
    Get a specific property for a column                                                            
        dbo.usp_ExtendedProperties 'dbo','tblCategory','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'                   
                                                                                                    
==================================================================================================*/
alter 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 = 'tblCategory'
*/    
/*---------------------------*/

/*--- Inits ---*/
declare @level3 sysname
    
set @level3 = case when @table is not null then 'column' else null end

/*--- Set Property Value (?) ---*/
if @schema is not null and @table is not null and @column is not null and @property is not null 
and @value is not null begin

    /*--- 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

    select 
         objtype
        ,objname
        ,name -- property name
        ,value
    from 
        ::fn_listextendedproperty(null,'schema', @schema, 'table', @table, @level3, @column)