USP_VALIDATION_REFERENTIALINTEGRITY

CREATE procedure BBDW.[USP_VALIDATION_REFERENTIALINTEGRITY] (@INCLUDEEXTENSIONS bit)

as

  declare @SCHEMA nvarchar(255);
  declare @TABLE nvarchar(255);
  declare @COLUMN nvarchar(255);
  declare @FKVALUE nvarchar(255);
  declare @FKCOLUMN nvarchar(255);
  declare @FKTABLE nvarchar(255);
  declare @FKSCHEMA nvarchar(255);
  declare @SQL nvarchar(max);
  declare @EXCEPTIONCOUNT int;
  declare @EXCEPTIONMESSAGE nvarchar(255);
  declare @TOTALEXCEPTIONCOUNT int = 0;

  declare [TABLECOLUMN] cursor local fast_forward for
  select 
    s.[name] as [SCHEMA],
    t.[name] as [TABLE], 
    c.[name] as [COLUMN],   
    cast(ep.[value] as nvarchar(255)) as [FKVALUE]
  from sys.extended_properties as ep
  inner join sys.tables as t on ep.major_id = t.object_id 
  inner join sys.schemas s on t.schema_id = s.schema_id
  inner join sys.columns as c on ep.major_id = c.object_id and ep.minor_id = c.column_id
  where class = 1 and ep.name = 'FK_REFERENCE' and 
  (c.[name] like '%DIMID' or c.[name] like '%FACTID') and
  (@INCLUDEEXTENSIONS = 1 or t.[name] not like '%_EXT') and
  (@INCLUDEEXTENSIONS = 1 or t.[name] not like 'USR_%');

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

  while(@@fetch_status <> -1)
  begin

    set @FKVALUE = replace(replace(@FKVALUE,'[',''), ']','');

    select
      @FKSCHEMA = left(@FKVALUE, charindex('.',@FKVALUE) - 1),
      @FKtable = substring(
        @FKVALUE, 
        charindex('.',@FKVALUE) + 1, 
        charindex('.',@FKVALUE, charindex('.',@FKVALUE) + 1) - (charindex('.',@FKVALUE) + 1)
      ),
      @FKCOLUMN = reverse(left(reverse(@FKVALUE), charIndex('.', reverse(@FKVALUE)) - 1));

    if exists(select 1 from INFORMATION_SCHEMA.[COLUMNS] where [TABLE_SCHEMA] = @FKSCHEMA and [TABLE_NAME] = @FKtable and [COLUMN_NAME] = @FKCOLUMN)
    begin
      set @SQL = 'select @EXCEPTIONCOUNT =  count(1) from ' + @SCHEMA + '.[' + @TABLE + '] f with (nolock)'
      set @SQL = @SQL + 'left join ' + @FKSCHEMA + '.' + @FKtable + ' d with (nolock) on f.[' + @COLUMN + '] = d.' + @FKCOLUMN
      set @SQL = @SQL + ' where d.' + @FKCOLUMN + ' is null'

      if @COLUMN like '%FACTID' 
        set @SQL = @SQL + ' and f.[' + @COLUMN + '] <> 0'

      exec sp_executesql @SQL, N'@EXCEPTIONCOUNT integer output', @EXCEPTIONCOUNT = @EXCEPTIONCOUNT output;

      if @EXCEPTIONCOUNT > 0
        begin
          set @EXCEPTIONMESSAGE = 'There are ' + cast(@EXCEPTIONCOUNT as nvarchar(10)) + ' row(s) in table [' + @SCHEMA + '].[' + @TABLE + '] with a value for [' + @COLUMN + '] that do not have a corresponding value in [' + @FKSCHEMA + '].[' + @FKtable + '].[' + @FKCOLUMN + ']'
          exec BBDW.[USP_VALIDATIONEXCEPTIONLOG] 'Referential Integrity', @EXCEPTIONMESSAGE
          set @TOTALEXCEPTIONCOUNT = @TOTALEXCEPTIONCOUNT + 1
        end

    end

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

  end  

  close [TABLECOLUMN];
  deallocate [TABLECOLUMN];

  select @TOTALEXCEPTIONCOUNT;