CREATE VIEW Metadata.ProcedureScripts
AS
WITH IndexColumns
AS (SELECT pc.OBJECT_ID,
s.NAME AS SchemaName,
pc.[Name] AS ObjectName,
ix.NAME AS IndexName,
ix.type_desc COLLATE SQL_Latin1_General_CP1_CI_AS AS IndexType,
ds.type AS DataspaceType,
ds.name AS DataspaceName,
ix.index_id,
ix.is_unique,
ix.is_primary_key,
ix.is_unique_constraint,
ix.is_disabled,
ix.filter_definition,
ix.has_filter,
ISNULL(' WITH (' + NULLIF(LTRIM(RTRIM(SUBSTRING(ISNULL(', ' + dc.DataCompression, '')
+ ISNULL(CASE WHEN ix.IGNORE_DUP_KEY = 1 THEN ', IGNORE_DUP_KEY = ON'
END, '')
+ ISNULL(CASE WHEN ix.ALLOW_ROW_LOCKS = 0 THEN ', ALLOW_ROW_LOCKS = OFF'
END, '')
+ ISNULL(CASE WHEN ix.ALLOW_PAGE_LOCKS = 0 THEN ', ALLOW_PAGE_LOCKS = OFF'
END, '') + ISNULL(CASE WHEN ix.fill_factor > 0
THEN ', FILLFACTOR = '
+ CAST(ix.fill_factor AS VARCHAR)
END, '')
+ ISNULL(CASE WHEN ix.is_padded > 0 THEN ', PAD_INDEX = ON'
END, ''), 3, 8000))), '') + ')', '') AS IndexOptions,
ic.KeyColumns,
ic.IncludeColumns,
ic.PartitionedColumn
FROM sys.views (NOLOCK) pc
INNER JOIN sys.schemas s (NOLOCK) ON s.SCHEMA_ID = pc.SCHEMA_ID
INNER JOIN sys.indexes AS ix (NOLOCK) ON pc.OBJECT_ID = ix.OBJECT_ID
AND ix.index_id > 0
LEFT OUTER JOIN sys.data_spaces AS ds (NOLOCK) ON ds.data_space_id = ix.data_space_id
LEFT OUTER JOIN sys.partitions AS pr (NOLOCK) ON pr.OBJECT_ID = pc.OBJECT_ID
AND pr.index_id = ix.index_id
AND pr.partition_number = 1
CROSS APPLY (SELECT CASE pr.data_compression_desc COLLATE SQL_Latin1_General_CP1_CI_AS
WHEN 'ROW' THEN 'DATA_COMPRESSION = ROW'
WHEN 'PAGE' THEN 'DATA_COMPRESSION = PAGE'
END AS DataCompression) AS dc
CROSS APPLY (SELECT Util.dbo.StringConcat(CASE WHEN key_ordinal > 0 THEN QUOTENAME(ColumnName) + CASE WHEN is_descending_key = 1 THEN ' DESC'
ELSE ''
END
END, ', ') AS KeyColumns,
Util.dbo.StringConcat(CASE WHEN is_included_column = 1 THEN QUOTENAME(ColumnName)
END, ', ') AS IncludeColumns,
MAX(CASE WHEN partition_ordinal = 1 THEN QUOTENAME(ColumnName)
END) AS PartitionedColumn
FROM (SELECT TOP 999999
cl.name AS ColumnName,
ic.key_ordinal,
ic.is_included_column,
ic.partition_ordinal,
ic.is_descending_key
FROM sys.index_columns AS ic WITH (NOLOCK)
INNER JOIN sys.columns AS cl WITH (NOLOCK) ON cl.OBJECT_ID = ic.OBJECT_ID
AND cl.column_id = ic.column_id
WHERE ic.OBJECT_ID = ix.OBJECT_ID
AND ic.index_id = ix.index_id
ORDER BY ic.key_ordinal,
ic.index_column_id) k) ic),
Indexes
AS (SELECT TOP 99999999
OBJECT_ID,
'IF NOT EXISTS (SELECT * FROM sys.schemas s (NOLOCK) INNER JOIN sys.objects o (NOLOCK) ON o.schema_id = s.schema_id INNER JOIN sys.indexes i (NOLOCK) ON i.object_id = o.object_id WHERE s.name = '''
+ SchemaName + ''' AND o.name = ''' + ObjectName + ''' AND i.name = ''' + IndexName + ''')
' + CASE WHEN has_filter = 1 THEN 'SET ANSI_NULLS ON
' ELSE ''
END + CASE WHEN is_primary_key = 1
OR is_unique_constraint = 1
THEN 'ALTER TABLE ' + QUOTENAME(SchemaName) + '.' + QUOTENAME(ObjectName) + ' ADD CONSTRAINT ' + QUOTENAME(IndexName) + ' '
+ CASE WHEN is_primary_key = 1 THEN 'PRIMARY KEY '
ELSE 'UNIQUE '
END + IndexType + ' (' + KeyColumns + ')' + IndexOptions + ' ' + CASE DataspaceType
WHEN 'FG' THEN 'ON ' + QUOTENAME(DataspaceName)
WHEN 'PS'
THEN 'ON ' + QUOTENAME(DataspaceName) + '(' + PartitionedColumn + ')'
WHEN 'FD' THEN ''
ELSE ''
END
ELSE 'CREATE ' + CASE WHEN is_unique = 1 THEN 'UNIQUE '
ELSE ''
END + IndexType + ' INDEX ' + QUOTENAME(IndexName) + ' ON ' + QUOTENAME(SchemaName) + '.' + QUOTENAME(ObjectName) + ' ('
+ KeyColumns + ')' + ISNULL(' INCLUDE (' + IncludeColumns + ')', '') + ISNULL(' WHERE ' + filter_definition, '') + IndexOptions + ' '
+ CASE DataspaceType
WHEN 'FG' THEN 'ON ' + QUOTENAME(DataspaceName) + ''
WHEN 'PS' THEN 'ON ' + QUOTENAME(DataspaceName) + '(' + PartitionedColumn + ')'
WHEN 'FD' THEN ''
ELSE ''
END
END + '
' AS Script
FROM IndexColumns ic),
indexfinal
AS (SELECT OBJECT_ID,
Util.dbo.StringConcat(Script, '
GO
') AS IndexScript
FROM Indexes i
GROUP BY OBJECT_ID)
--#endregion
--#region Objects create
SELECT SCHEMA_ID AS ObjectId,
'SCHEMA' AS [Type],
QUOTENAME(s.NAME) AS FQN,
s.name AS SchemaName,
s.NAME AS ObjectName,
'SCHEMA' AS CreateDefinition,
'CREATE SCHEMA [' + s.name + '] AUTHORIZATION [' + d.name + ']' AS [Definition],
'ALTER AUTHORIZATION ON SCHEMA::[' + s.name + '] TO [' + d.name + ']' AS AlterScript,
NULL AS IndexScript,
NULL AS AnsiSQL,
'DROP SCHEMA [' + s.name + ']' AS DropScript,
CAST(NULL AS VARCHAR(256)) AS AssemblyName,
CAST(NULL AS DATETIME) AS CreateDate,
CAST(NULL AS DATETIME) AS ModifyDate
FROM sys.schemas s (NOLOCK)
INNER JOIN sys.database_principals d (NOLOCK) ON s.principal_id = d.principal_id
UNION ALL
SELECT o.OBJECT_ID AS ObjectId,
o.TYPE COLLATE SQL_Latin1_General_CP1_CI_AS AS [Type],
QUOTENAME(s.NAME) + '.' + QUOTENAME(o.name) AS FQN,
s.name AS SchemaName,
o.name AS ObjectName,
ot.CreateDefinition,
m.Definition,
NULL AS AlterScript,
di.IndexScript,
'SET ANSI_NULLS ' + CASE WHEN m.uses_ansi_nulls = 1 THEN 'ON'
ELSE 'OFF'
END + '
SET QUOTED_IDENTIFIER ' + CASE WHEN uses_quoted_identifier = 1 THEN 'ON'
ELSE 'OFF'
END AS AnsiSQL,
'DROP ' + ot.CreateDefinition COLLATE SQL_Latin1_General_CP1_CI_AS + ' ' + QUOTENAME(S.NAME COLLATE SQL_Latin1_General_CP1_CI_AS) + '.'
+ QUOTENAME(o.name COLLATE SQL_Latin1_General_CP1_CI_AS) AS DropScript,
CAST(NULL AS VARCHAR(256)) AS AssemblyName,
o.create_date AS CreateDate,
o.modify_date AS ModifyDate
FROM sys.schemas s (NOLOCK)
INNER JOIN sys.objects o (NOLOCK) ON o.SCHEMA_ID = s.SCHEMA_ID
INNER JOIN Util.dbo.SystemObjectTypes ot ON ot.Type = o.type COLLATE SQL_Latin1_General_CP1_CI_AS
INNER JOIN sys.all_sql_modules m (NOLOCK) ON m.OBJECT_ID = o.OBJECT_ID
LEFT OUTER JOIN indexfinal di ON m.is_schema_bound = 1
AND di.OBJECT_ID = o.OBJECT_ID
WHERE ot.CreateDefinition IN ('PROCEDURE', 'VIEW', 'TRIGGER', 'FUNCTION')
AND o.is_ms_shipped = 0
AND s.name COLLATE SQL_Latin1_General_CP1_CI_AS NOT IN ('cdc', 'sys')
AND NOT (s.name COLLATE SQL_Latin1_General_CP1_CI_AS = 'dbo'
AND o.NAME COLLATE SQL_Latin1_General_CP1_CI_AS LIKE 's%diagram%')
UNION ALL
SELECT m.OBJECT_ID AS ObjectId,
o.TYPE COLLATE SQL_Latin1_General_CP1_CI_AS AS [Type],
QUOTENAME(s.NAME) + '.' + QUOTENAME(o.name) AS FQN,
s.name AS SchemaName,
o.name AS ObjectName,
ot.CreateDefinition,
fd.Definition,
fd.AlterScript,
CAST(NULL AS VARCHAR(MAX)) AS IndexScript,
CAST(NULL AS VARCHAR(MAX)) AS AnsiSQL,
'DROP ' + ot.CreateDefinition + ' ' + FQN AS DropScript,
a.name COLLATE SQL_Latin1_General_CP1_CI_AS AS AssemblyName,
o.create_date AS CreateDate,
o.modify_date AS ModifyDate
FROM sys.assembly_modules m (NOLOCK)
INNER JOIN sys.assemblies a (NOLOCK) ON a.assembly_id = m.assembly_id
INNER JOIN sys.objects o (NOLOCK) ON o.OBJECT_ID = m.OBJECT_ID
INNER JOIN sys.schemas s (NOLOCK) ON s.SCHEMA_ID = o.SCHEMA_ID
CROSS APPLY (SELECT QUOTENAME (s.name) + '.' + QUOTENAME (o.name) AS FQN) f
LEFT OUTER JOIN sys.database_principals dp (NOLOCK) ON dp.principal_id = m.execute_as_principal_id
LEFT OUTER JOIN util.dbo.SystemObjectTypes ot ON ot.Type = o.type COLLATE SQL_Latin1_General_CP1_CI_AS
CROSS APPLY (SELECT 'RETURNS TABLE (
' + Util.dbo.StringConcat(' ' + QUOTENAME(c.NAME) + ' ' + ct.ColumnType + CASE WHEN c.is_nullable = 1 THEN ' NULL'
ELSE ' NOT NULL'
END, ',
') + '
)' AS ReturnTable
FROM sys.columns c (NOLOCK)
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
WHERE c.OBJECT_ID = m.OBJECT_ID
AND o.type = 'FT' -- Assembly (CLR) table-valued function
) rt
CROSS APPLY (SELECT '(' + Util.dbo.StringConcat(FuncInParam, ', ') + ')' AS FuncInParams,
'RETURNS ' + Util.dbo.StringConcat(FuncOutParam, ',') AS FuncOutParam,
Util.dbo.StringConcat(' ' + ProcParam, ',
') AS ProcParams
FROM sys.parameters pr (NOLOCK)
INNER JOIN sys.types y (NOLOCK) ON y.user_type_id = pr.user_type_id
INNER JOIN sys.databases AS db (NOLOCK) ON db.database_id = DB_ID()
CROSS APPLY Util.dbo.GetColumnType(y.name, pr.max_length, pr.PRECISION, pr.scale, db.collation_name, db.collation_name) ct
CROSS APPLY (SELECT CASE WHEN pr.parameter_id = 0 THEN ct.ColumnType
END AS FuncOutParam,
CASE WHEN pr.parameter_id > 0 THEN pr.name COLLATE SQL_Latin1_General_CP1_CI_AS + ' ' + ct.ColumnType
END AS FuncInParam,
CASE WHEN pr.parameter_id > 0
THEN pr.name COLLATE SQL_Latin1_General_CP1_CI_AS + ' ' + ct.ColumnType + CASE WHEN pr.has_default_value = 1
THEN ' = '
+ CASE WHEN y.name LIKE '%char'
OR y.name = 'sysname'
OR y.name = 'xml'
THEN ISNULL(REPLACE(CAST (default_value AS VARCHAR(MAX)),
'''', ''''''),
'NULL')
WHEN y.name LIKE '%date%'
OR y.name LIKE '%time%'
THEN ISNULL(CAST (pr.default_value AS VARCHAR(MAX)),
'NULL')
WHEN y.collation_name IS NULL
AND pr.PRECISION > 0
AND y.name NOT LIKE '%date%'
AND y.name NOT LIKE '%time%'
THEN CAST (pr.default_value AS VARCHAR)
ELSE ISNULL(CAST (default_value AS VARCHAR(MAX)),
'NULL')
END
ELSE ''
END + CASE WHEN is_output = 1 THEN ' OUTPUT'
WHEN is_readonly = 1
THEN ' READONLY'
ELSE ''
END
END AS ProcParam) p2
WHERE pr.OBJECT_ID = m.OBJECT_ID) p2
CROSS APPLY (SELECT 'WITH EXECUTE AS ' + ISNULL(QUOTENAME(dp.name), 'CALLER') AS ExecAs,
'EXTERNAL NAME ' + QUOTENAME(a.NAME COLLATE SQL_Latin1_General_CP1_CI_AS) + '.' + QUOTENAME(m.assembly_class COLLATE SQL_Latin1_General_CP1_CI_AS)
+ ISNULL('.' + QUOTENAME(m.assembly_method COLLATE SQL_Latin1_General_CP1_CI_AS), '') AS ExtName) ea
CROSS APPLY (SELECT CASE WHEN ot.CreateDefinition = 'AGGREGATE' THEN ot.CreateDefinition + ' ' + FQN + '
' + ISNULL(FuncInParams, '()') + '
' + FuncOutParam + '
' + ExtName WHEN ot.CreateDefinition = 'FUNCTION' THEN ot.CreateDefinition + ' ' + FQN + '
' + ISNULL(FuncInParams, '()') + '
' + CASE WHEN o.type = 'FT' THEN ReturnTable
ELSE FuncOutParam
END + '
' + ExecAs + CASE WHEN m.null_on_null_input = 1 THEN ', RETURNS NULL ON NULL INPUT'
ELSE ''
END + '
AS
' + ExtName WHEN ot.CreateDefinition = 'PROCEDURE' THEN ot.CreateDefinition + ' ' + FQN + '
' + ProcParams + '
' + ExecAs + '
AS
' + ExtName
END AS Def) dd
CROSS APPLY (SELECT 'CREATE ' + dd.Def AS Definition,
CASE WHEN CreateDefinition = 'AGGREGATE' THEN 'DROP AGGREGATE ' + FQN + '
EXEC(''' + 'CREATE ' + dd.Def + ''')'
ELSE 'ALTER ' + dd.Def
END AS AlterScript) fd
WHERE a.is_user_defined = 1
AND o.is_ms_shipped = 0