USP_VALIDATION_NULLSINCOLUMNS

create procedure [BBDW].[USP_VALIDATION_NULLSINCOLUMNS] (@INCLUDEEXTENSIONS bit)
as

  declare @SCHEMA nvarchar(255);
  declare @TABLE nvarchar(255);
  declare @COLUMN nvarchar(255);
  declare @SQL nvarchar(max);

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

  declare @RESULT table ([SCHEMA] nvarchar(255), [TABLE] nvarchar(255), [COLUMN] nvarchar(255), [COUNT] int, [SQL] nvarchar(max));

  declare TABLECOLUMN 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.columns c on c.object_id = t.object_id
      inner join sys.types ty on c.user_type_id = ty.user_type_id
      left join sys.extended_properties as ep on ep.major_id = t.object_id and ep.minor_id = c.[column_id] and ep.[name] = 'NULLABLE'
    where
      (cast(ep.value as int) <> 1 or ep.value is null) and       
      (t.[name] like 'DIM_%' or t.[name] like 'FACT_%' or (@INCLUDEEXTENSIONS = 1 and t.[name] like 'USR_%')) and
      (ty.[name] not in ('datetime', 'datetimeoffset', 'date', 'time')) and
      (@INCLUDEEXTENSIONS = 1 or t.[name] not like '%_EXT')
      order by t.[name], c.[name];

  open TABLECOLUMN;    
  fetch next from TABLECOLUMN into @SCHEMA, @TABLE, @COLUMN;

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

    set @SQL = 'set @EXCEPTIONCOUNT = (select count(1) from [' + @SCHEMA + '].[' + @TABLE + '] t with (nolock) where t.[' + @COLUMN + '] is null)';
    exec sp_executesql @SQL, N'@EXCEPTIONCOUNT int output', @EXCEPTIONCOUNT = @EXCEPTIONCOUNT output;

    if @EXCEPTIONCOUNT > 0
      begin
        set @EXCEPTIONMESSAGE = 'There are ' + cast(@EXCEPTIONCOUNT as nvarchar(10)) + ' row(s) with null values in table: [' +  @SCHEMA + '].[' + @TABLE + '], column: [' + @COLUMN + ']'
        exec BBDW.[USP_VALIDATIONEXCEPTIONLOG] 'Null Values in Columns', @EXCEPTIONMESSAGE
        set @TOTALEXCEPTIONCOUNT = @TOTALEXCEPTIONCOUNT + 1
      end

    fetch next from TABLECOLUMN into @SCHEMA, @TABLE, @COLUMN;
  end  

  select @TOTALEXCEPTIONCOUNT;