USP_VALIDATION_MISSINGCOMMENTS

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

declare @EXCEPTIONS as int;
declare @TABLE as table([OBJECTNAME] nvarchar(255), [COLUMNNAME] nvarchar(255));

--Check for missing comments on tables
insert into @TABLE([OBJECTNAME])
  select [TABLE] 
  from
      (
    select 
        s.[name] as [SCHEMA],
        t.[name] as [TABLE]
      from sys.tables as t
      inner join sys.schemas s on t.schema_id = s.schema_id
      inner join sys.columns as c on c.object_id = t.object_id
      where 
    (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')

      except 

    select 
      s.[name] as [SCHEMA],
      t.[name] as [TABLE]
    from sys.extended_properties ep
    inner join sys.tables t on ep.major_id = t.object_id
    inner join sys.schemas s on t.schema_id = s.schema_id
    where ep.[class] = 1 and ep.[name] = 'MS_Description'
    and ep.[minor_id] = 0 and len(cast(ep.[value] as nvarchar(max))) > 0
      ) as [COMMENTS];

insert into BBDW.[VALIDATIONEXCEPTIONS]([TESTNAME], [EXCEPTION])
  select 'Missing Comments', 'The table [' + [OBJECTNAME] + '] does not contain a comment'
  from @TABLE;

set @EXCEPTIONS = (select count(1) from @TABLE);

delete from @TABLE;

--Check for missing comments on table columns
insert into @TABLE([OBJECTNAME], [COLUMNNAME])
  select [TABLE], [COLUMN]
  from
      (
      select
        s.[name] as [SCHEMA],
        t.[name] as [TABLE], 
        c.[name] as [COLUMN]
      from sys.tables as t
      inner join sys.schemas s on t.schema_id = s.schema_id
      inner join sys.columns as c on c.object_id = t.object_id
      where (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')

      except 

      select 
        s.[name] as [SCHEMA],
        t.[name] as [TABLE], 
        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] = 'MS_Description'
      and len(cast(ep.[value] as nvarchar(max))) > 0
      ) as [COMMENTS]

insert into BBDW.[VALIDATIONEXCEPTIONS]([TESTNAME], [EXCEPTION])
  select 'Missing Comments', 'The column ['+ [COLUMNNAME] + '] on table [' + [OBJECTNAME] + '] does not contain a comment'
  from @TABLE;

set @EXCEPTIONS = @EXCEPTIONS + (select count(1) from @TABLE);

delete from @TABLE;

--Check for missing comments on views
insert into @TABLE([OBJECTNAME])
  select [VIEW]
  from
      (
    select 
        s.[name] as [SCHEMA],
        v.[name] as [VIEW]
      from sys.views as v
      inner join sys.schemas s on v.schema_id = s.schema_id
      inner join sys.columns as c on c.object_id = v.object_id
      where 
      (v.[name] like 'v_DIM_%' or v.[name] like 'v_FACT_%' or (@INCLUDEEXTENSIONS = 1 and (v.[name] like 'USR_v_DIM_%' or v.[name] like 'USR_v_FACT_%')))
      and 
      (@INCLUDEEXTENSIONS = 1 or v.[name] not like '%_EXT')

      except 

    select 
      s.[name] as [SCHEMA],
      v.[name] as [VIEW]
    from sys.extended_properties ep
    inner join sys.views v on ep.major_id = v.object_id
    inner join sys.schemas s on v.schema_id = s.schema_id
    where ep.[class] = 1 and ep.[name] = 'MS_Description'
    and ep.[minor_id] = 0 and len(cast(ep.[value] as nvarchar(max))) > 0
      ) as [COMMENTS];

insert into BBDW.[VALIDATIONEXCEPTIONS]([TESTNAME], [EXCEPTION])
  select 'Missing Comments', 'The view [' + [OBJECTNAME] + '] does not contain a comment'
  from @TABLE;

set @EXCEPTIONS = (select count(1) from @TABLE);

delete from @TABLE;

--Check for missing comments on view columns
insert into @TABLE([OBJECTNAME], [COLUMNNAME])
  select [VIEW], [COLUMN] 
  from
      (
      select
        s.[name] as [SCHEMA],
        v.[name] as [VIEW], 
        c.[name] as [COLUMN]
      from sys.views as v
      inner join sys.schemas s on v.schema_id = s.schema_id
      inner join sys.columns as c on c.object_id = v.object_id
      where 
      (v.[name] like 'v_DIM_%' or v.[name] like 'v_FACT_%' or (@INCLUDEEXTENSIONS = 1 and (v.[name] like 'USR_v_DIM_%' or v.[name] like 'USR_v_FACT_%')))
      and 
      (@INCLUDEEXTENSIONS = 1 or v.[name] not like '%_EXT')

      except 

      select 
        s.[name] as [SCHEMA],
        v.[name] as [VIEW], 
        c.[name] as [COLUMN]
      from sys.extended_properties as ep
      inner join sys.views as v on ep.major_id = v.object_id 
      inner join sys.schemas s on v.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] = 'MS_Description'
      and len(cast(ep.[value] as nvarchar(max))) > 0
      ) as [COMMENTS];

insert into BBDW.[VALIDATIONEXCEPTIONS]([TESTNAME], [EXCEPTION])
  select 'Missing Comments', 'The column ['+ [COLUMNNAME] + '] on view [' + [OBJECTNAME] + '] does not contain a comment'
  from @TABLE;

set @EXCEPTIONS = @EXCEPTIONS + (select count(1) from @TABLE);

select @EXCEPTIONS;