USP_VALIDATION_MISSINGCOLUMNSTOREINDEXES

CREATE procedure [BBDW].[USP_VALIDATION_MISSINGCOLUMNSTOREINDEXES] (@INCLUDEEXTENSIONS bit) as

  declare @TABLENAME nvarchar(255);
  declare @SCHEMA nvarchar(255);
  declare @EXCEPTIONMESSAGE nvarchar(255);
  declare @TOTALEXCEPTIONCOUNT int = 0;

  declare @TABLES table ([NAME] nvarchar(255), [SCHEMA] nvarchar(255));

  insert into @TABLES ([NAME], [SCHEMA])
    select
      t.[name],
      s.[name]
    from 
      sys.tables t
      inner join sys.schemas s on s.schema_id = t.schema_id
      inner join sys.extended_properties as ep on ep.major_id = t.object_id and ep.minor_id = 0
    where 
      ep.name = 'MS_Description' and cast(ep.value as nvarchar(max)) not like 'DEPRECATED%'
      and (t.[name] like 'FACT_%' or t.[name] like 'DIM_%')
      and t.[name] not like '%_STAGE'
      and t.[name] not in ('FACT_SYSTEMROLEAPPUSER','FACT_FINANCIALTRANSACTIONLINEITEMSITE', 'FACT_CONSTITUENTSITE')
      and (@INCLUDEEXTENSIONS = 1 or t.[name] not like '%_EXT' or t.[name] not like 'USR_%')        
      and t.[name] <> 'DIM_IDSETREGISTER'


  declare [TABLECURSOR] cursor local fast_forward for
    select 
      [NAME],
      [SCHEMA]
    from @TABLES;

  open [TABLECURSOR];    
  fetch next from [TABLECURSOR] into @TABLENAME, @SCHEMA;

  while(@@fetch_status <> -1)
  begin

    if not exists (select i.* from sys.indexes i inner join sys.objects o on i.object_id = o.object_id where o.name = @TABLENAME and i.type = 6)          
    begin

      set @EXCEPTIONMESSAGE = 'There is not a non-clustered columnstore index on  [' + @SCHEMA + '].[' + @TABLENAME + ']';
      exec BBDW.[USP_VALIDATIONEXCEPTIONLOG] 'Missing Columnstore Index', @EXCEPTIONMESSAGE;
      set @TOTALEXCEPTIONCOUNT = @TOTALEXCEPTIONCOUNT + 1;

    end

    fetch next from [TABLECURSOR] into @TABLENAME, @SCHEMA;

  end  

  close [TABLECURSOR];
  deallocate [TABLECURSOR];

  select @TOTALEXCEPTIONCOUNT;