Manage Extended Property

create       proc sp_extendedpropertymethod
    (@name sysname              = null,
    @value sql_variant          = null,
    @level0type varchar(128)    = null,
    @level0name sysname         = null,
    @level1type varchar(128)    = null,
    @level1name sysname         = null,
    @level2type varchar(128)    = null,
    @level2name sysname         = null,
    @method     varchar(8)      = 'list'
)
as
/**************************************************************
File            : dbo.sp_extendedpropertymethod.sql
Name            : sp_extendedpropertymethod
Author          : Peter Lin 
Date            : 2003-05-21
Description     :sp_extendedpropertymethod wraps following stored procedures and function:
                 sp_addextendedproperty, sp_dropextendedproperty,sp_updateextendproperty
                 ::fn_listextendedproperty. These procedures can only add/update/drop property
                 to a single database object.
                 In order to use extend properties in real development, we should be able to
                 manage extended properties in a more efficient and flexible manner. 
                 This wrapper procedure let the user using a wildcard character like '%' 
                 while specifying object name parameters: level0name, level1name, level2name. 
                 The procedure will first build an affected db object list 
                 by querying system catalog tables using the pattern specified by the passed in parameters
                 and then call corresponding system procedures for all objects in the list.
Referencing      :1) sp_addextendedproperty
                  2) sp_dropextendedproperty
                  3) sp_updateextendedproperty
                  4) fn_listextendedproperty
                  5) sysobjects
                  6) sysindexes
                  7) systypes
                  8) sysusers
                  9) syscolumns
Referenced By    : 
Parameter Input    : NOTE: the first eight parameters have the same semantics as those in the above listed
                  system stored procedures except that @level0name, @level1name and @level2name
                  can also take a pattern as their input.
                   1) [ @name = ] { 'property_name' }
                        A. When the @method parameter is 'list'
                            Is the name of the property. property_name is sysname. 
                            Valid inputs are default, NULL, or a property name.
                        B. When the @method parameter is add/drop/update
                            Is the name of the property to be added/dropped/updated. 
                            property_name is sysname and cannot be NULL. 
                            Names may also include blank or non-alphanumeric character strings
                            and binary values.
                   2) [ @value = ] { 'value' }
                        Is the value to be associated with the property. value is sql_variant,
                        with a default of NULL. The size of value may not be more than
                        7,500 bytes; otherwise, SQL Server raises an error.
                   3) [ @level0type = ] { 'level0_object_type' }
                        Is the user or user-defined type. level0_object_type is varchar(128),
                        with a default of NULL. Valid inputs are USER, TYPE, and NULL.
                   4) [ @level0name = ] { 'level0_object_name' }
                        Is the name or pattern of the level 0 object type specified. 
                        level0_object_name is sysname with a default of NULL.
                   5) [ @level1type = ] { 'level1_object_type' }
                        Is the type of level 1 object. level1_object_type is varchar(128),
                        with a default of NULL.
                        Valid inputs are TABLE, VIEW, PROCEDURE, FUNCTION, DEFAULT, RULE, and NULL.
                   6) [ @level1name = ] { 'level1_object_name' }
                       Is the name or pattern of the level 1 object type specified. 
                       level1_object_name is sysname, with a default of NULL.
                   7) [ @level2type = ] { 'level2_object_type' }
                       Is the type of level 2 object. level2_object_type is varchar(128),
                       with a default of NULL. 
                       Valid inputs are COLUMN, PARAMETER, INDEX, CONSTRAINT, TRIGGER, and NULL.
                   8) [ @level2name = ] { 'level2_object_name' }
                       Is the name or pattern of the level 2 object type specified. 
                       level2_object_name is sysname, with a default of NULL.
                   9) [@method=] {'method'}
                        Is the action you want the stored procedure to perform,
                        with a default of 'list'.
                        Valid inputs are 'add','update','drop','list'
                        If you specify 'list', the @name parameter can be NULL.
                        If the value for property_name is NULL or default, 
                        sp_extendedpropertymethod returns 
                        all the properties for the selected objects
Parameter Output:
Return Value    : 0 -- success
                  1 -- failure
                    if @method = 'list', a table in following format is returned
                    This is also the format of the tables returned by fn_listextendedproperty, 
                    as sp_extendproperty is using fn_listextendedproperty to retrieve properties info
                    ----------------------------------
                    | Column name    | Data type     | 
                    ----------------------------------
                    | objtype        | sysname       |
                    | objname        | sysname       |
                    | name           | sysname       |
                    | value          | sql_variant   |
                    ----------------------------------
                If the table returned is empty, either the object does not have extended properties 
                or the user does not have permissions to list the extended properties on the object.
Remarks:        wildcard characters only can be used in an object name parameter: 
                level0name, level1name, level2name to specify a pattern
                wildcard characters CANNOT be used in a property name and type parameters: 
                level0type, level1type, level2type
                Please refer to description of the LIKE operator in BOL for more information 
                about search pattern you can specify here
----------------------------------------------------------------
History

Author        :
Date          :
Description   :

----------------------------------------------------------------
Examples
    USE Northwind
    -- add a property named 'FormTitle' to every view with a name like 'Order%' in the database
    EXEC sp_extendedpropertymethod 'FormTitle','put window title here','USER',null,'VIEW','Order%',null,null,'add'
    -- list all properties of view objects in the database 
    EXEC sp_extendedpropertymethod null,null, 'USER',null,'VIEW',null,null,null,'list'
    -- drop property 'FormTitle' of view with a name like 'Orders%' in the database
    EXEC sp_extendedpropertymethod 'FormTitle','put window title here', 'USER',null,'VIEW','Orders%',null,null,'drop'
    -- drop property 'FormTitle' of all views in the database, if the view don't have such a property, an error will be returned
    EXEC sp_extendedpropertymethod 'FormTitle','put window title here', 'USER',null,'VIEW',null,null,null,'drop'

    -- add a property named 'EnCaption' to all columns of TABLEs 
    EXEC sp_extendedpropertymethod 'EnCaption','put form control caption here', 'USER',null,'TABLE',null,'COLUMN',null,'add'
    -- list a property named 'EnCaption' to all columns with a name like 'customer%' of all TABLEs with a name like  'customer%'
    EXEC sp_extendedpropertymethod 'EnCaption',null, 'USER',null,'TABLE','Customer%','COLUMN','Customer%','list'
    -- drop a property named 'EnCaption' to all columns of TABLEs 
    EXEC sp_extendedpropertymethod 'EnCaption',null, 'USER',null,'TABLE',null,'COLUMN',null,'drop'

    -- add/list/drop a property named 'Remarks' to all stored procedures
    EXEC sp_extendedpropertymethod 'Remarks','put your stored procedure remarks here', 'USER',null,'PROCEDURE',null,null,null,'add'
    EXEC sp_extendedpropertymethod 'Remarks','put your stored procedure remarks here', 'USER',null,'PROCEDURE',null,null,null,'list'
    EXEC sp_extendedpropertymethod 'Remarks','put your stored procedure remarks here', 'USER',null,'PROCEDURE',null,null,null,'drop'
    -- add/list/drop a property named 'Author(s)' to all stored procedures
    EXEC sp_extendedpropertymethod 'Author(s)','put your stored procedure remarks here', 'USER',null,'PROCEDURE',null,null,null,'add'
    EXEC sp_extendedpropertymethod 'Author(s)','put your stored procedure remarks here', 'USER',null,'PROCEDURE',null,null,null,'list'
    EXEC sp_extendedpropertymethod 'Author(s)','put your stored procedure remarks here', 'USER',null,'PROCEDURE',null,null,null,'drop'

****************************************************************/
    set nocount on
    declare @objtype varchar(2)
    declare @ret int

    if @method not in ('add','update','drop','list')
    begin
        raiserror('The parameter %s must be one of ''add'',''update'',''drop'',''list'''
                ,-1,-1,'@method')
        return 1
    end
    if @method <> 'list'
    begin
        if @name is null
        begin
            raiserror ('The parameter %s cannot be null when using ''%s'' method'
                ,-1,-1,'@name',@method) --more specific error msg
            return 1
        end
        else
        begin
            execute @ret = sp_validname @name
            if (@ret <> 0)
                return 1
        end
    end
       
    if @method = 'list'
    begin
        declare @tab table(
                objtype varchar(128)    null,
                objname sysname         null,
                name sysname            not null,
                value sql_variant       null
        )
    end

    if datalength(@value) > 7500
    begin
        raiserror(15097,-1,-1)
        return 1
    end

    declare @ptab table (
        name        sysname         null,
        value       sql_variant     null,
        level0type  varchar(128)    null,
        level0name  sysname         null,
        level1type  varchar(128)    null,
        level1name  sysname         null,
        level2type  varchar(128)    null,
        level2name  sysname         null
    )

    select @level0type     = UPPER(@level0type)
           ,@level1type    = UPPER(@level1type)
           ,@level2type    = UPPER(@level2type)

    if @level0type is null
    begin
        if @level1type is null and    @level2type is null
        begin
            if is_member('db_owner') = 0 
            begin
                raiserror(15247,-1,-1)
                return 1
            end

            insert @ptab
            select @name,@value,@level0type,@level0name,
                @level1type,@level1name,@level2type,@level2name    
        end

        goto DOWORK
    end

    if @level1type is null and @level2type is null
    begin
        if @level0type = 'TYPE'
        begin
            insert @ptab
            select @name,@value,@level0type,name,
                @level1type,@level1name,@level2type,@level2name
            from systypes
            where (@level0name is null or name like @level0name) and
                xusertype > 256 --only udtypes

        end
        else if @level0type = 'USER'
        begin
            insert @ptab
            select @name,@value,@level0type,name,
                @level1type,@level1name,@level2type,@level2name    
            from sysusers
            where (@level0name is null or name like @level0name) and
              (is_member('db_owner') = 1 or is_member(name) = 1) and
              (issqluser = 1 or isntname = 1) and
                -- Not permitted to add prop to 
               -- dbo/guest/INFORMATION_SCHEMA/system_function_schema
              uid NOT IN (1,2,3,4) 
        end
        goto DOWORK
    end

    if @level0type is null or @level0type <> 'USER'
        return

    if @level2type is null
    begin
        if @level1type in ('TABLE', 'VIEW', 'PROCEDURE', 'RULE', 'DEFAULT')
        begin
            select @objtype = case @level1type
                        when 'TABLE' then 'U'
                        when 'VIEW' then 'V'
                        when 'PROCEDURE' then 'P'
                        when 'RULE' then 'R'
                        when 'DEFAULT' then 'D'
                        end

            insert @ptab
            select @name,@value,@level0type,u.name,
                @level1type,o.name,@level2type,@level2name    
            from (select uid, name 
                  from  sysusers 
                  where (is_member('db_owner') = 1 or is_member(name) = 1) and
                    (@level0name is null or name like @level0name) and
                    -- no INFORMATION_SCHEMA/system_function_schema
                    uid NOT IN (3,4) 
                    ) as u, 
                 (select uid,name 
                  from sysobjects
                  where (permissions(id) > 0 or @objtype in ('R', 'D')) and
                    xtype = @objtype and parent_obj = 0 and
                    (@level1name is null or name like @level1name)
                    ) as o
            where u.uid = o.uid
        end
        else if (@level1type = 'FUNCTION')
        begin
 
            insert @ptab
            select @name,@value,@level0type,u.name,
                @level1type,o.name,@level2type,@level2name    
            from (select uid, name 
                  from  sysusers 
                  where (is_member('db_owner') = 1 or is_member(name) = 1) and
                  (@level0name is null or name like @level0name) and
                   uid NOT IN (3,4) 
                  ) as u, 
                 (select uid,name
                  from sysobjects
                  where permissions(id) > 0  and
                    xtype in ('TF','FN','IF') and
                    (@level1name is null or name like @level1name)
                  ) as o
            where u.uid = o.uid    
        end
        goto DOWORK
    end

    if @level1type is null
        return

    if @level2type = 'COLUMN'
    begin

        if @level1type not in ('TABLE', 'VIEW', 'FUNCTION')
            return

        if @level1type in ('TABLE','VIEW')
        begin
            select @objtype = case @level1type
                        when 'TABLE' then 'U'
                        when 'VIEW' then 'V'
                        end

            insert @ptab
            select @name,@value,@level0type,u.name,
                @level1type,o.name,@level2type,c.name    
            from (select uid, name 
                  from  sysusers 
                  where (is_member('db_owner') = 1 or is_member(name) = 1) and
                    (@level0name is null or name like @level0name) and
                     uid NOT IN (3,4) 
                    ) as u, 
                 (select uid,name,id 
                  from sysobjects
                  where (permissions(id) > 0) and
                    xtype = @objtype and parent_obj = 0 and
                    (@level1name is null or name like @level1name)
                     ) as o,
                  (select id,name
                  from syscolumns
                  where permissions(id,name) > 0 and number = 0 and
                    (@level2name is null or name like @level2name)
                    ) as c
            where u.uid = o.uid and
                  o.id = c.id    
        
        end
        else if @level1type = 'FUNCTION'
        begin
            insert @ptab
            select @name,@value,@level0type,u.name,
                @level1type,o.name,@level2type,c.name    
            from (select uid, name 
                 from  sysusers 
                 where     (is_member('db_owner') = 1 or is_member(name) = 1) and
                    (@level0name is null or name like @level0name) and
                    uid NOT IN (3,4) 
                    ) as u, 
                (select uid,name,id 
                 from sysobjects
                 where (permissions(id) > 0) and
                    xtype in ('TF','IF') and
                    (@level1name is null or name like @level1name)
                    ) as o,
                (select id,name
                 from syscolumns
                 where permissions(id,name) > 0 and number = 0 and
                    (@level2name is null or name like @level2name)
                    ) as c
            where u.uid = o.uid and
                  o.id = c.id    
        end            
    
    end
    else if @level2type ='TRIGGER'
    begin
        if @level1type not in ('TABLE', 'VIEW')
            return

        select @objtype = case @level1type
                    when 'TABLE' then 'U'
                    when 'VIEW' then 'V'
                    end
        insert @ptab
        select @name,@value,@level0type,u.name,
            @level1type,o.name,@level2type,o2.name    
        from (select uid, name 
             from  sysusers 
             where     (is_member('db_owner') = 1 or is_member(name) = 1) and
                (@level0name is null or name like @level0name) and
                uid NOT IN (3,4) 
                ) as u, 
            (select uid,name,id 
             from sysobjects
             where (permissions(id) > 0) and
                xtype = @objtype and parent_obj = 0 and
                (@level1name is null or name like @level1name)
                ) as o,
            (select name,parent_obj
             from sysobjects
             where    xtype = 'TR' and
                (@level2name is null or name like @level2name)
                ) as o2
        where u.uid = o.uid and
              o.id = o2.parent_obj    
    
    end
    else if @level2type = 'CONSTRAINT'
    begin
        if @level1type not in ('TABLE', 'FUNCTION')
            return

        if @level1type ='TABLE'
        begin
            insert @ptab
            select @name,@value,@level0type,u.name,
                @level1type,o.name,@level2type,o2.name    
            from (select uid, name 
                 from  sysusers 
                 where (is_member('db_owner') = 1 or is_member(name) = 1) and
                    (@level0name is null or name like @level0name) and
                     uid NOT IN (3,4) 
                     ) as u, 
                (select uid,name,id 
                 from sysobjects
                 where (permissions(id) > 0) and
                   xtype = 'U' and parent_obj = 0 and
                   (@level1name is null or name like @level1name)
                   ) as o,
                (select name,parent_obj
                 from sysobjects
                 where    xtype in ('C','D','F','PK','UQ') and
                    (@level2name is null or name like @level2name)
                    ) as o2
            where u.uid = o.uid and
                    o.id = o2.parent_obj    
        
        end
        else if @level1type = 'FUNCTION'
        begin
            insert @ptab
            select @name,@value,@level0type,u.name,
                @level1type,o.name,@level2type,o2.name    
            from (select uid, name 
                  from  sysusers 
                  where (is_member('db_owner') = 1 or is_member(name) = 1) and
                    (@level0name is null or name like @level0name) and
                    uid NOT IN (3,4) 
                    ) as u, 
                 (select uid,name,id 
                  from sysobjects
                  where (permissions(id) > 0) and
                    xtype in ('TF','IF') and
                    (@level1name is null or name like @level1name)
                    ) as o,
                 (select name,parent_obj
                  from sysobjects
                  where xtype in ('C','D','F','PK','UQ') and
                    (@level2name is null or name like @level2name)
                    ) as o2
            where u.uid = o.uid and
                  o.id = o2.parent_obj    
        end            
    end
    else if @level2type = 'INDEX'
    begin
        if @level1type not in ('TABLE', 'VIEW')
            return

        select @objtype = case @level1type
                    when 'TABLE' then 'U'
                    when 'VIEW' then 'V'
                    end

        insert @ptab
        select @name,@value,@level0type,u.name
            ,@level1type,o.name,@level2type,i.name    
        from (select uid, name 
             from  sysusers 
             where     (is_member('db_owner') = 1 or is_member(name) = 1) and
                (@level0name is null or name like @level0name) and
                uid NOT IN (3,4) 
                ) as u, 
            (select uid,name,id 
             from sysobjects
             where (permissions(id) > 0) and
                xtype = @objtype and parent_obj = 0 and
                (@level1name is null or name like @level1name)
                ) as o,
            (select id,name
             from sysindexes
             where     (@level2name is null or name like @level2name) and
                indid not in (0,255) and status&0x1800 = 0 -- no PK/UQ constraint
                ) as i
        where u.uid = o.uid and
              o.id = i.id    
        
    end
    else if @level2type = 'PARAMETER'
    begin
        if @level1type not in ('PROCEDURE', 'FUNCTION')
            return

        if @level1type = 'PROCEDURE'
        begin
            insert @ptab
            select @name,@value,@level0type,u.name,
                @level1type,o.name,@level2type,c.name    
            from (select uid, name 
                 from  sysusers 
                 where     (is_member('db_owner') = 1 or is_member(name) = 1) and
                    (@level0name is null or name like @level0name) and
                    uid NOT IN (3,4) 
                    ) as u, 
                (select uid,name,id 
                 from sysobjects
                 where (permissions(id) > 0) and
                    xtype = 'P' and parent_obj = 0 and
                    (@level1name is null or name like @level1name)
                    ) as o,
                (select id,name
                 from syscolumns
                 where  number = 1 and
                    (@level2name is null or name like @level2name)
                    ) as c
            where u.uid = o.uid and
                  o.id = c.id    
        end
        else if @level1type = 'FUNCTION'
        begin
            insert @ptab
            select @name,@value,@level0type,u.name,
                @level1type,o.name,@level2type,c.name    
            from (select uid, name 
                 from  sysusers 
                 where (is_member('db_owner') = 1 or is_member(name) = 1) and
                    (@level0name is null or name like @level0name) and
                    uid NOT IN (3,4) 
                    ) as u, 
                (select uid,name,id,xtype 
                 from sysobjects
                 where (permissions(id) > 0) and
                    xtype in ('TF','IF','FN') and
                    (@level1name is null or name like @level1name)
                    ) as o,
                (select id,name,number
                 from syscolumns
                 where     @level2name is null or name like @level2name
                    ) as c
            where u.uid = o.uid and
                  o.id = c.id and
                  (c.number = 1 or (c.number = 0 and o.xtype='FN'))                      
        end                
    end

    DOWORK:
        
    --do the work
    if exists(select * from @ptab) 
    begin
        DECLARE para_cursor cursor local fast_forward for
            select     name, value,level0type,level0name
                ,level1type,level1name,level2type,level2name    
            from @ptab
        OPEN para_cursor
         FETCH next from para_cursor into @name, @value,@level0type,@level0name,
                    @level1type,@level1name,@level2type,@level2name
        
        WHILE (@@fetch_status <> -1)
        BEGIN
            if @method = 'add'
            execute sp_addextendedproperty @name, @value,@level0type,@level0name,
                    @level1type,@level1name,@level2type,@level2name
            else if @method = 'drop'
            execute sp_dropextendedproperty @name, @level0type,@level0name,
                    @level1type,@level1name,@level2type,@level2name
            else if @method = 'update'
            execute sp_updateextendedproperty @name, @value,@level0type,@level0name,
                    @level1type,@level1name,@level2type,@level2name
            else if @method = 'list'
            begin
            insert into @tab
            select objtype,objname,name,value 
            from ::fn_listextendedproperty (@name,@level0type,@level0name,
                    @level1type,@level1name,@level2type,@level2name)
            end
            
             FETCH next from para_cursor into @name, @value,@level0type,@level0name,
                    @level1type,@level1name,@level2type,@level2name

        END
        CLOSE para_cursor    
        DEALLOCATE para_cursor

        if @method = 'list'
            select objtype,objname,name,value from @tab
            
    end     
    return