USP_SCHEMA_INDEX_SETISSYTEMFLAG

CREATE procedure [dbo].[USP_SCHEMA_INDEX_SETISSYTEMFLAG]
    @TABLENAME nvarchar(128),
    @INDEXNAME nvarchar(128),
    @PARENTISVIEW bit = 0
as

declare @value sql_variant;


--select @value = value from fn_listextendedproperty(N'BB_IsSystem', N'SCHEMA', N'dbo', N'table', @TABLENAME, N'index', @INDEXNAME)

-- pdg 11.5.2009 optimized to use sys.extended_properties rather than the slower fn_listextendedproperty (which internally uses sys.extended_properties).

select @value = ep.value from sys.extended_properties as ep 
inner join sys.indexes as ix on ep.major_id=ix.[object_id] and ep.minor_id=ix.index_id
where ep.class=7 and ep.major_id=OBJECT_ID(@TABLENAME) and ep.minor_id > 0 and ep.[name]=N'BB_IsSystem' and ix.[name]=@INDEXNAME;

declare @parentObjectType nvarchar(max);
if @PARENTISVIEW = 1 
    set @parentObjectType = N'view';
else
    set @parentObjectType = N'table';

if @value is null
    -- property doesn't exist, so add it
    exec sys.sp_addextendedproperty N'BB_IsSystem', '1', N'SCHEMA', N'dbo', @parentObjectType, @TABLENAME, N'index', @INDEXNAME;
 else if @value <> '1'  
    -- property already exists, so update it
    exec sys.sp_updateextendedproperty N'BB_IsSystem', '1', N'SCHEMA', N'dbo', @parentObjectType, @TABLENAME, N'index', @INDEXNAME;