;
WITH s AS (SELECT QUOTENAME(NAME) AS SchemaName,
name,
SCHEMA_ID
FROM sys.schemas
WHERE name NOT IN ('guest', 'INFORMATION_SCHEMA', 'sys', 'cdc', 'tTransMat', 'tTaxMat', 'tTransProp', 'tTaxProp', 'DS')
AND name NOT LIKE '%-%'
AND Name NOT LIKE '%switch'
AND Name NOT LIKE '%AUDIT%'
AND name NOT LIKE 'tTax[0-9][0-9]'),
o AS (SELECT s.SCHEMA_ID,
o.OBJECT_ID,
s.SchemaName,
QUOTENAME(o.NAME) AS ObjectName,
QUOTENAME(s.name) + '.' + QUOTENAME(o.name) AS FQN,
ot.CreateDefinition AS Type
FROM s
INNER JOIN sys.objects o (NOLOCK) ON o.SCHEMA_ID = s.SCHEMA_ID
INNER JOIN Util.dbo.SystemObjectTypes ot ON o.type COLLATE SQL_Latin1_General_CP1_CI_AS = ot.Type
WHERE ot.CreateDefinition IN ('TABLE', 'FUNCTION', 'PROCEDURE')
AND ot.CreateOrder IS NOT NULL
AND NOT (s.name = 'dbo'
AND o.name LIKE 'sys%'))
SELECT SCHEMA_ID AS SchemaId,
NULL AS ObjectId,
NULL AS FieldId,
SchemaName,
SchemaName AS Objectname,
SchemaName AS Name,
SchemaName AS FQN,
NULL AS ColumnType,
'SCHEMA' AS [Type]
FROM s
UNION ALL
SELECT SCHEMA_ID AS SchemaId,
OBJECT_ID AS ObjectId,
NULL AS FieldId,
SchemaName,
ObjectName,
ObjectName AS Name,
FQN,
NULL AS ColumnType,
Type
FROM o
UNION ALL
SELECT o.SCHEMA_ID AS SchemaId,
o.OBJECT_ID AS ObjectId,
c.Column_id AS FieldId,
o.SchemaName,
o.ObjectName,
QUOTENAME(c.name) AS NAME,
o.FQN + '.' + QUOTENAME(c.name) AS FQN,
ct.ColumnType + CASE WHEN c.is_identity = 1 THEN ' IDENTITY'
ELSE ''
END + CASE WHEN c.is_nullable = 1 THEN ' NULL'
ELSE ' NOT NULL'
END AS ColumnType,
'COLUMN' AS Type
FROM o
INNER JOIN sys.columns c (NOLOCK) ON o.OBJECT_ID = c.OBJECT_ID
INNER JOIN sys.types y (NOLOCK) ON y.user_type_id = c.user_type_id
INNER JOIN sys.databases AS db (NOLOCK) ON db.database_id = DB_ID()
CROSS APPLY Util.dbo.GetColumnType(y.name, c.max_length, c.PRECISION, c.scale, c.collation_name, db.collation_name) ct
UNION ALL
SELECT o.SCHEMA_ID AS SchemaId,
o.OBJECT_ID AS ObjectId,
C.parameter_id AS FieldId,
o.SchemaName,
o.ObjectName,
c.name AS Name,
o.FQN + '.' + c.name AS FQN,
ct.ColumnType ColumnType,
'PARAMETER' AS Type
FROM o
INNER JOIN sys.parameters c (NOLOCK) ON o.OBJECT_ID = c.OBJECT_ID
INNER JOIN sys.types y (NOLOCK) ON y.user_type_id = c.user_type_id
INNER JOIN sys.databases AS db (NOLOCK) ON db.database_id = DB_ID()
CROSS APPLY Util.dbo.GetColumnType(y.name, c.max_length, c.PRECISION, c.scale, db.collation_name, db.collation_name) ct