CREATE VIEW Metadata.Columns
AS
SELECT o.Type,
ot.TypeName,
QUOTENAME(s.name) + '.' + QUOTENAME(o.name) AS FQN,
s.name AS SchemaName,
o.name AS ObjectName,
c.name AS ColumnName,
ct.ColumnType AS ColumnDef,
c.column_id AS ColumnId,
ic.key_ordinal AS PKOrdinal,
c.is_nullable AS IsNullable,
c.is_identity AS IsIdentity,
c.max_length AS MaxLength,
c.is_computed AS IsComputed,
dc.definition AS DefaultDefinition,
ck.definition AS CheckDefinition,
cc.DEFINITION AS ComputeDefinition,
o.OBJECT_ID AS ObjectId
FROM sys.schemas s (NOLOCK)
INNER JOIN sys.objects o (NOLOCK) ON s.SCHEMA_ID = o.SCHEMA_ID
INNER JOIN sys.columns c (NOLOCK) ON c.OBJECT_ID = o.OBJECT_ID
LEFT OUTER JOIN Util.dbo.SystemObjectTypes ot ON ot.Type = o.TYPE COLLATE SQL_Latin1_General_CP1_CI_AS
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()
LEFT OUTER JOIN sys.indexes i (NOLOCK) ON i.OBJECT_ID = o.OBJECT_ID
AND i.is_primary_key = 1
LEFT OUTER JOIN sys.index_columns ic (NOLOCK) ON ic.OBJECT_ID = o.OBJECT_ID
AND ic.index_id = i.index_id
AND ic.column_id = c.column_id
LEFT OUTER JOIN sys.default_constraints dc (NOLOCK) ON dc.parent_object_id = o.OBJECT_ID
AND dc.parent_column_id = c.column_id
LEFT OUTER JOIN sys.check_constraints ck (NOLOCK) ON ck.parent_object_id = o.OBJECT_ID
AND ck.parent_column_id = c.column_id
LEFT OUTER JOIN sys.computed_columns cc (NOLOCK) ON cc.OBJECT_ID = o.OBJECT_ID
AND cc.column_id = c.column_id
CROSS APPLY Util.dbo.GetColumnType(y.name, c.max_length, c.PRECISION, c.scale, c.collation_name, db.collation_name) ct
WHERE o.is_ms_shipped = 0