USP_VALIDATION_GRANULARITY

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

  declare @SCHEMA nvarchar(255);
  declare @TABLE nvarchar(255);
  declare @COLUMN nvarchar(255);
  declare @SQL nvarchar(max) = '';
  declare @SELECTSQL nvarchar(max) = '';
  declare @FROMSQL nvarchar(max) = '';
  declare @GROUPSQL nvarchar(max) = '';
  declare @HAVINGSQL nvarchar(max) = '';
  declare @EXCEPTIONCOUNT integer;
  declare @EXCEPTIONMESSAGE nvarchar(255);
  declare @TOTALEXCEPTIONCOUNT int = 0;

  declare [TABLECURSOR] cursor local fast_forward for

  select 
    distinct
    s.[name] as [SCHEMA],
    t.[name] as [TABLE]
  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 
  ep.[class] = 1 and 
  ep.[name] = 'INCLUDEINGRAIN' and 
  ep.[value] = '1' and
  (t.[NAME] like 'DIM_%' or t.[NAME] like 'FACT_%' or (@INCLUDEEXTENSIONS = 1 and t.[NAME] like 'USR_%')) and
  (@INCLUDEEXTENSIONS = 1 or t.[name] not like '%_EXT');

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

  while(@@fetch_status <> -1)
  begin

    declare [COLUMNCURSOR] cursor local fast_forward for

    select 
      distinct
      c.[name] as [COLUMN]
    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 
    ep.[class] = 1 and 
    ep.[name] = 'INCLUDEINGRAIN' and 
    ep.[value] = '1' and
    t.[name] = @TABLE and s.[name] = @SCHEMA;    

    open [COLUMNCURSOR];
    fetch next from [COLUMNCURSOR] into @COLUMN;

    while(@@fetch_status <> -1)  

    begin
      if @SELECTSQL = ''
        begin
          set @SELECTSQL = 'select @EXCEPTIONCOUNT = count(1) from (select ' + @COLUMN 
          set @FROMSQL = ' from ' + @SCHEMA + '.' + @TABLE + ' with (nolock) '
          set @GROUPSQL =  ' group by ' + @COLUMN 
          set @HAVINGSQL = ' having count(*) > 1) as [GRAINTEST]'
        end
      else
        begin
          set @SELECTSQL = @SELECTSQL + ', ' + @COLUMN
          set @GROUPSQL = @GROUPSQL + ', ' + @COLUMN
        end

    fetch next from [COLUMNCURSOR] into @COLUMN;
    end

    close [COLUMNCURSOR];
    deallocate [COLUMNCURSOR];

    set @SQL = @SELECTSQL + @FROMSQL + @GROUPSQL + @HAVINGSQL
        exec sp_executesql @SQL, N'@EXCEPTIONCOUNT integer output', @EXCEPTIONCOUNT = @EXCEPTIONCOUNT output;

    if @EXCEPTIONCOUNT > 0
      begin
      set @EXCEPTIONMESSAGE = 'There are ' + cast(@EXCEPTIONCOUNT as nvarchar(10)) + ' record(s) in table [' + @SCHEMA + '].[' + @TABLE + '] that have more than one row. The granularity of the table is based on the following column(s):' + replace(@GROUPSQL,'group by','')
          exec BBDW.[USP_VALIDATIONEXCEPTIONLOG] 'Granularity', @EXCEPTIONMESSAGE
          set @TOTALEXCEPTIONCOUNT = @TOTALEXCEPTIONCOUNT + 1
      end

    set @SELECTSQL = ''
    set @FROMSQL = ''
    set @GROUPSQL = ''
    set @HAVINGSQL = ''
    set @EXCEPTIONCOUNT = 0

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

  close [TABLECURSOR];
  deallocate [TABLECURSOR];

  select @TOTALEXCEPTIONCOUNT;