USP_VALIDATION_DEFAULTDIMENSIONROWS

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

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

declare [DIMTABLE] cursor local fast_forward for
  select    
    s.[name] [TABLE_SCEMA],
    t.[name] [TABLE_NAME],
    c.[name] [COLUMN_NAME]
  from 
    sys.tables t
    inner join sys.schemas s on s.[schema_id] = t.[schema_id]
    inner join sys.columns c on c.[object_id] = t.[object_id]
    inner join sys.extended_properties as ep on ep.[major_id] = t.[object_id] and ep.[minor_id] = 0
    inner join sys.index_columns ic on ic.[column_id] = c.[column_id] and ic.[object_id] = t.[object_id]
    inner join sys.indexes i on ic.[object_id] = i.[object_id] and ic.[index_id] = i.[index_id]
  where
    i.[is_primary_key] = 1 and
    ep.[name] = 'MS_Description' and cast(ep.[value] as nvarchar(max)) not like 'DEPRECATED%' and
    (t.[name] like 'DIM_%' or (@INCLUDEEXTENSIONS = 1 and t.[name] like 'USR_%')) and
    t.[name] not like '%_STAGE' and
    (@INCLUDEEXTENSIONS = 1 or t.[name] not like '%_EXT');

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

while(@@fetch_status <> -1)
begin
  set @TEST = 0;

  set @SQL = 'select @TEST = 1 from [' + @SCHEMA + '].[' + @TABLE + '] t with (nolock) where t.[' + @COLUMN + '] = 0;';

  exec sp_executesql @SQL, N'@TEST int output', @TEST = @TEST output;

  if @TEST = 0
    begin
      set @EXCEPTIONMESSAGE = 'There is no default row on dimension table [' + @SCHEMA + '].[' + @TABLE + ']'
      exec BBDW.[USP_VALIDATIONEXCEPTIONLOG] 'Missing Default Dimension Row', @EXCEPTIONMESSAGE
      set @TOTALEXCEPTIONCOUNT = @TOTALEXCEPTIONCOUNT + 1
     end

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

select @TOTALEXCEPTIONCOUNT;