CREATE_OR_DROP_INDICES

CREATE procedure [BBDW].[CREATE_OR_DROP_INDICES]
  @SCHEMA nvarchar(128),
  @TABLENAME nvarchar(128),
  @CREATE_OR_DROP bit, --1 to create, 0 to drop
  @ADD_COLUMNSTORE bit 
as
  set nocount on;

  --Get SQL version
  declare @SQLVersion int = substring(cast(serverproperty('ProductVersion') AS nvarchar), 1, charindex('.', cast(serverproperty('ProductVersion') AS nvarchar)) - 1);

  --Set the table type
  declare @TABLETYPE tinyint; --0 = STAGE, 1 = DIM, 2 = FACT
  declare @FILEGROUPNAME nvarchar(128);

  if @TABLENAME like '%_STAGE'
  begin
    set @TABLETYPE = 0;
    set @FILEGROUPNAME = 'BBRPT_STAGEGROUP';
  end
  else if @TABLENAME like 'DIM_%'
  begin
    set @TABLETYPE = 1;
    set @FILEGROUPNAME = 'BBRPT_DIMIDXGROUP';
  end
  else if @TABLENAME like 'FACT_%' or @TABLENAME like 'RPT_%'
  begin
    set @TABLETYPE = 2;
    set @FILEGROUPNAME = 'BBRPT_FACTIDXGROUP';
  end

  declare @SQL nvarchar(max);
  declare @COLUMNNAME nvarchar(128);
  declare @DATATYPE nvarchar(128);
  declare @INDEXNAME nvarchar(128);

  --All the eligible columns on the table, excluding the primary key column
  declare COLUMNCURSOR cursor local fast_forward for
    select
      c.[COLUMN_NAME],
      c.[DATA_TYPE]
    from INFORMATION_SCHEMA.COLUMNS c
    where 
      c.[TABLE_SCHEMA] = @SCHEMA
      and c.[TABLE_NAME] = @TABLENAME
      and 
      (
        c.[COLUMN_NAME] like '%SYSTEMID'
        or
        (c.[COLUMN_NAME] like '%DIMID' and @TABLETYPE <> 0)
        or
        (c.[COLUMN_NAME] like '%FACTID' and @TABLETYPE <> 0)
        or
        (c.[COLUMN_NAME] like '%LOOKUPID' and @TABLETYPE <> 0)
        or
        (c.[COLUMN_NAME] like '%SEQUENCEID' and @TABLETYPE <> 0)
      )
    except
    select   
      c.[COLUMN_NAME],
      c.[DATA_TYPE]
    from     
      INFORMATION_SCHEMA.TABLE_CONSTRAINTS tab
      inner join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE col on col.[CONSTRAINT_NAME] = tab.[CONSTRAINT_NAME] and col.[TABLE_SCHEMA] = tab.[TABLE_SCHEMA] and col.[TABLE_NAME] = tab.[TABLE_NAME]
      inner join INFORMATION_SCHEMA.COLUMNS c on col.COLUMN_NAME = c.COLUMN_NAME and col.TABLE_NAME = c.TABLE_NAME and col.TABLE_SCHEMA = c.TABLE_SCHEMA
    where
      tab.[CONSTRAINT_TYPE] = 'PRIMARY KEY'
      and tab.[TABLE_SCHEMA] = @SCHEMA
      and tab.[TABLE_NAME] = @TABLENAME;

  open COLUMNCURSOR;
  fetch next from COLUMNCURSOR into @COLUMNNAME, @DATATYPE;

  --Loop through all the columns to create/drop nonclustered indexes
  while (@@FETCH_STATUS = 0)
  begin
    set @INDEXNAME = 'IX_' + @TABLENAME + '_' + @COLUMNNAME;

    --Create nonclustered indexes
    if @CREATE_OR_DROP = 1
    begin

      --SQL 2012+ - Create noclustered indexes on uniqueidentifier columns and remove all other nonclustered indexes
      if @SQLVersion >= 11
      begin

        --Create indexes for all eligible columns that are also uniqueidentifier
        if @DATATYPE = 'uniqueidentifier'
        begin

          --Create index if it doesn't already exist
          if [BBDW].[UFN_INDEXEXISTS](@INDEXNAME) = 0
          begin
            set @SQL = 'create index [' + @INDEXNAME + '] on [' + @SCHEMA + '].[' + @TABLENAME + ']([' + @COLUMNNAME + ']) with ( DATA_COMPRESSION = PAGE ) on [' + @FILEGROUPNAME +'];';
            exec sp_executesql @SQL; 
          end          
        end
        --Drop all existing nonclustered indexes that will be put into the column store index
        else
        begin

          --Drop index if it exist
          if [BBDW].[UFN_INDEXEXISTS](@INDEXNAME) = 1
          begin
            set @SQL = 'drop index [' + @INDEXNAME + '] on [' + @SCHEMA + '].[' + @TABLENAME + '];';
            exec sp_executesql @SQL; 
          end
        end

      end

    end    
    --Drop nonclustered indexes
    else
    begin

      --Drop nonclustered index if it exist
      if [BBDW].[UFN_INDEXEXISTS](@INDEXNAME) = 1
      begin
        set @SQL = 'drop index [' + @INDEXNAME + '] on [' + @SCHEMA + '].[' + @TABLENAME + '];';
        exec sp_executesql @SQL; 
      end     
    end

    fetch next from COLUMNCURSOR into @COLUMNNAME, @DATATYPE;
  end
  close COLUMNCURSOR;
  deallocate COLUMNCURSOR;  

  --SQL 2012+ - Create/Drop column store index
  if @CREATE_OR_DROP = 1 and @ADD_COLUMNSTORE = 1 and @SQLVersion >= 11
  begin
    --Create columnstore index
    exec BBDW.[USP_CREATECOLUMNSTOREINDEX] @SCHEMA, @TABLENAME;  
  end
  else if @CREATE_OR_DROP = 0 and @SQLVersion >= 11
  begin
    --Drop columnstore index  
    exec BBDW.[USP_DROPCOLUMNSTOREINDEX] @SCHEMA, @TABLENAME;
  end