USP_VALIDATION_GETDATATYPES

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

  select 
    s.[name] + '.' + t.[name] + '.' + c.[name] as [DWTABLE],
    ep.[value] as [COMMENT],
    ty.[name] as [DATATYPE],
    isnull(c.[max_length], 0) as [LENGTH],
    isnull(c.[precision], 0) as [PRECISION],
    isnull(c.[scale], 0) as [SCALE],
    left(cast(ep.[value] as nvarchar(max)), charindex('.',cast(ep.[value] as nvarchar(max))) - 1) as [SCHEMA],
    substring(cast(ep.[value] as nvarchar(max)), 
    charindex('.',cast(ep.[value] as nvarchar(max))) + 1, 
    charindex('.',cast(ep.[value] as nvarchar(max)), 
    charindex('.',cast(ep.[value] as nvarchar(max))) + 1) - 
    (charindex('.',cast(ep.[value] as nvarchar(max))) + 1)) as [TABLE],
    reverse(left(reverse(cast(ep.[value] as nvarchar(max))), charIndex('.', reverse(cast(ep.[value] as nvarchar(max)))) - 1)) 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
  inner join sys.types ty on c.user_type_id = ty.system_type_id and c.user_type_id = ty.user_type_id
  where class = 1 and ep.name = 'MS_Description'
  and cast(ep.[value] as nvarchar(max)) like '%dbo%'
  and cast(ep.[value] as nvarchar(max))  not like '% %'
  and cast(ep.[value] as nvarchar(max))  like '%.%.%'
  and c.[max_length] <> -1 --nvarchar(max)
  and (@INCLUDEEXTENSIONS = 1 or t.[name] not like '%_EXT')
  and (@INCLUDEEXTENSIONS = 1 or t.[name] not like 'USR_%');