CREATE VIEW Metadata.Objects
AS
WITH OBJECTS
AS (SELECT s.name AS SchemaName,
o.name AS ObjectName,
QUOTENAME(s.NAME) + '.' + QUOTENAME(o.name) AS FQN,
o.type COLLATE SQL_Latin1_General_CP1_CI_AS AS [Type],
ot.CreateDefinition AS TypeName,
dp.name AS Principal,
o.create_date AS CreateDate,
o.modify_date AS ModifyDate,
o.is_published AS IsPublished,
o.is_schema_published AS IsSchemaPublished,
o.OBJECT_ID AS ObjectId,
o.parent_object_id AS ParentObjectId
FROM sys.schemas s (NOLOCK)
INNER JOIN sys.objects o (NOLOCK) ON o.SCHEMA_ID = s.SCHEMA_ID
LEFT OUTER JOIN Util.dbo.SystemObjectTypes ot ON ot.Type = o.type COLLATE SQL_Latin1_General_CP1_CI_AS
LEFT OUTER JOIN sys.database_principals dp (NOLOCK) ON o.principal_id IS NOT NULL
AND dp.principal_id = o.principal_id
WHERE is_ms_shipped = 0)
SELECT c.FQN,
c.SchemaName,
c.ObjectName,
c.Type,
c.TypeName,
c.Principal,
c.CreateDate,
c.ModifyDate,
c.IsPublished,
c.IsSchemaPublished,
p.SchemaName AS ParentSchemaName,
p.ObjectName AS ParentObjectName,
p.Type AS ParentType,
p.TypeName AS ParentTypeName,
p.Principal AS ParentPrincipal,
p.CreateDate AS ParentCreateDate,
p.ModifyDate AS ParentModifyDate,
p.IsPublished AS ParentIsPublished,
p.IsSchemaPublished AS ParentIsSchemaPublished,
c.ObjectId,
c.ParentObjectId
FROM OBJECTS c
LEFT OUTER JOIN OBJECTS p ON c.ParentObjectId <> 0
AND p.ObjectId = c.ParentObjectId