USP_VALIDATION_MISSINGINDEXES

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

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

  declare [MISSINGINDEX] cursor local fast_forward for

  select
    s.[name] [SCHEMA],
    t.[name] [TABLE],
    c.[name] [COLUMN]
  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
    inner join sys.columns c on c.object_id = t.object_id
  where 
    ep.name = 'MS_Description' and cast(ep.value as nvarchar(max)) not like 'DEPRECATED%' and
    (t.[name] like 'DIM_%' or t.[name] like 'FACT_%' or (@INCLUDEEXTENSIONS = 1 and t.[name] like 'USR_%')) and
    (c.[name] like '%DIMID' or c.[name] like '%FACTID%') and
    t.[name] not like '%_STAGE' and
    c.[name] <> 'SOURCEDIMID' and
    (@INCLUDEEXTENSIONS = 1 or t.[name] not like '%_EXT')

  except

  select 
    s.name as [SCHEMA], 
    t.name as [TABLE], 
    c.name as [COLUMN]
  from sys.tables t
    inner join sys.schemas s on t.schema_id = s.schema_id
    inner join sys.indexes i on i.object_id = t.object_id
    inner join sys.index_columns ic on ic.object_id = t.object_id
    inner join sys.columns c on c.object_id  = t.object_id and ic.index_id = i.index_id and ic.column_id = c.column_id;


  open [MISSINGINDEX];    
  fetch next from [MISSINGINDEX] into @SCHEMA, @TABLE, @COLUMN;

  while(@@fetch_status <> -1)

  begin

      set @EXCEPTIONMESSAGE = 'There is no index on [' + @SCHEMA + '].[' + @TABLE + '].[' + @COLUMN + ']'
      exec BBDW.[USP_VALIDATIONEXCEPTIONLOG] 'Missing Indexes', @EXCEPTIONMESSAGE
      set @TOTALEXCEPTIONCOUNT = @TOTALEXCEPTIONCOUNT + 1


  fetch next from [MISSINGINDEX] into @SCHEMA, @TABLE, @COLUMN;

  end

  close [MISSINGINDEX];
  deallocate [MISSINGINDEX];

  select @TOTALEXCEPTIONCOUNT;