USP_VALIDATION_MISSINGMETADATACOLUMNS

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

declare @TABLE as table([SCHEMANAME] nvarchar(255), [TABLENAME] nvarchar(255))

insert into @TABLE([SCHEMANAME], [TABLENAME])

select
  [SCHEMA],
  [TABLE]
from
(  
  select
    t.[TABLE_SCHEMA] as [SCHEMA],
    t.[TABLE_NAME] as [TABLE]
  from INFORMATION_SCHEMA.TABLES t
  where     
    (t.[TABLE_NAME] like 'DIM_%' or t.[TABLE_NAME] like 'FACT_%' or (@INCLUDEEXTENSIONS = 1 and t.[TABLE_NAME] like 'USR_%'))
    and t.[TABLE_NAME] not like '%_STAGE%'
    and t.[TABLE_TYPE] = 'BASE TABLE'
    and (@INCLUDEEXTENSIONS = 1 or t.[TABLE_NAME] not like '%_EXT')

  except

  select
    col.[SCHEMA],
    col.[TABLE]
    from 
    (
    select
    c.[TABLE_SCHEMA] as [SCHEMA],
    c.[TABLE_NAME] as [TABLE],
    c.[COLUMN_NAME] as [COLUMN]
  from INFORMATION_SCHEMA.COLUMNS c  

  where     
    c.[COLUMN_NAME] in ('ETLCONTROLID')
    or c.[COLUMN_NAME] in ('ISINCLUDED')
    or c.[COLUMN_NAME] in ('SOURCEDIMID')
    ) col
    group by  col.[SCHEMA], col.[TABLE]
    having count(col.[COLUMN]) = 3
) as [METADATA]

insert into BBDW.[VALIDATIONEXCEPTIONS]([TESTNAME], [EXCEPTION])
  select 'Missing MetaData', '[' + [SCHEMANAME] + '].[' + [TABLENAME] + '] does not contain one or more of the following metadata columns: [ETLCONTROLID], [ISINCLUDED], [SOURCEDIMID]'
  from @TABLE;

select count(1) from @TABLE;