Jasinski Technical Wiki

Navigation

Home Page
Index
All Pages

Quick Search
»
Advanced Search »

Contributor Links

Create a new Page
Administration
File Management
Login/Logout
Your Profile

Other Wiki Sections

Software

PoweredBy

Extended Properties Stored Procedure - SQL Server

RSS
Modified on Mon, Nov 21, 2011, 1:54 PM by Administrator Categorized as SQL Server
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-2018, Patrick Jasinski.