CREATE VIEW Metadata.Assemblies
AS
WITH DepOrd
AS (SELECT assembly_id,
1 AS OrderId
FROM sys.assembly_references a (NOLOCK)
WHERE NOT EXISTS ( SELECT *
FROM sys.assembly_references b (NOLOCK)
WHERE b.referenced_assembly_id = a.assembly_id )
UNION ALL
SELECT referenced_assembly_id AS assembly_id,
S.OrderId + 1 AS OrderId
FROM sys.assembly_references a (NOLOCK)
INNER JOIN DepOrd s ON s.assembly_id = a.assembly_id),
s2
AS (SELECT assembly_id,
MAX(orderID) AS OrderId
FROM DepOrd s
GROUP BY assembly_id),
OrdFinal
AS (SELECT MAX(s2.OrderId) OVER (PARTITION BY (SELECT 0)) + 1 - s2.OrderId AS OrderId,
a.name,
a.assembly_id
FROM s2
INNER JOIN sys.assemblies a (NOLOCK) ON a.assembly_id = s2.assembly_id
WHERE a.is_user_defined = 1
UNION
SELECT 1 AS OrderId,
name,
assembly_id
FROM sys.assemblies a (NOLOCK)
WHERE a.is_user_defined = 1
AND NOT EXISTS ( SELECT *
FROM sys.assembly_references r (NOLOCK)
WHERE r.referenced_assembly_id = a.assembly_id
OR r.assembly_id = a.assembly_id ))
SELECT s.name AS AssemblyName,
d.name AS Principal,
CASE s.permission_set
WHEN 1 THEN 'SAFE'
WHEN 2 THEN 'EXTERNAL_ACCESS'
WHEN 3 THEN 'UNSAFE '
ELSE 'N/A'
END AS PermSQL,
s.PERMISSION_SET AS PermissionSet,
s.PERMISSION_SET_DESC COLLATE SQL_Latin1_General_CP1_CI_AS AS PermissionSetDesc,
s.clr_name COLLATE SQL_Latin1_General_CP1_CI_AS AS CLRName,
ISNULL(f.OrderId, 1) AS CreateOrderId,
af.CONTENT,
s.create_date AS CreateDate,
S.modify_date AS ModifyDate
FROM sys.assemblies s (NOLOCK)
LEFT OUTER JOIN OrdFinal f ON f.assembly_id = s.assembly_id
INNER JOIN sys.database_principals d (NOLOCK) ON s.principal_id = d.principal_id
INNER JOIN sys.assembly_files af (NOLOCK) ON af.assembly_id = s.assembly_id
CROSS APPLY (SELECT CONVERT(VARCHAR(MAX), af.CONTENT, 1) AS CharContent,
CASE s.permission_set
WHEN 1 THEN 'SAFE'
WHEN 2 THEN 'EXTERNAL_ACCESS'
WHEN 3 THEN 'UNSAFE '
ELSE 'N/A'
END AS Perm) z
WHERE s.is_user_defined = 1
AND af.FILE_ID = 1