CREATE VIEW Metadata.ObjectScripts
AS
SELECT [Type] COLLATE SQL_Latin1_General_CP1_CI_AS AS [Type],
FQN,
SchemaName,
ObjectName,
NULL AS SecondaryName,
NULL AS IndexId,
CreateDefinition,
Definition AS CreateScript,
DropScript,
AlterScript,
IndexScript,
AnsiSQL,
AssemblyName,
CreateDate,
ModifyDate,
ObjectId
FROM Metadata.ProcedureScripts
UNION ALL
SELECT [Type] COLLATE SQL_Latin1_General_CP1_CI_AS AS [Type],
FQN,
SchemaName,
TableName AS ObjectName,
CASE WHEN [Type] COLLATE SQL_Latin1_General_CP1_CI_AS <> 'U' THEN ObjectName
END AS SecondaryName,
CASE WHEN IndexId > 0 THEN IndexId
END AS IndexId,
CASE [Type] COLLATE SQL_Latin1_General_CP1_CI_AS
WHEN 'AF' THEN 'AGGREGATE'
WHEN 'FS' THEN 'FUNCTION'
WHEN 'FT' THEN 'FUNCTION'
WHEN 'PG' THEN 'PLAN GUIDE'
WHEN 'PC' THEN 'PROCEDURE'
WHEN 'RF' THEN 'PROCEDURE'
WHEN 'X' THEN 'PROCEDURE'
WHEN 'SQ' THEN 'QUEUE'
WHEN 'R' THEN 'RULE'
WHEN 'IT' THEN 'TABLE'
WHEN 'S' THEN 'TABLE'
WHEN 'TA' THEN 'TRIGGER'
WHEN 'U' THEN 'TABLE'
WHEN 'PK' THEN 'PRIMARY KEY'
WHEN 'D' THEN 'DEFAULT'
WHEN 'UQ' THEN 'UNIQUE CONSTRAINT'
WHEN 'C' THEN 'CHECK'
WHEN 'F' THEN 'FOREIGN KEY'
WHEN 'TR' THEN 'TRIGGER'
WHEN 'TT' THEN 'TYPE'
WHEN 'SN' THEN 'SYNONYM'
WHEN 'V' THEN 'VIEW'
WHEN 'FN' THEN 'FUNCTION'
WHEN 'IF' THEN 'FUNCTION'
WHEN 'TF' THEN 'FUNCTION'
WHEN 'P' THEN 'PROCEDURE'
WHEN 'I' THEN 'INDEX'
END COLLATE SQL_Latin1_General_CP1_CI_AS AS CreateDefinition,
CreateScript COLLATE SQL_Latin1_General_CP1_CI_AS AS CreateScript,
DropScript COLLATE SQL_Latin1_General_CP1_CI_AS AS DropScript,
NULL AS AlterScript,
NULL AS IndexScript,
NULL AS AnsiSQL,
NULL AS AssemblyName,
CreateDate,
ModifyDate,
ObjectId
FROM Metadata.TableScripts