CREATE VIEW Metadata.TableScripts
AS
WITH Tables
AS (SELECT t.OBJECT_ID,
QUOTENAME(s.name) + '.' + QUOTENAME(t.name) AS FQN,
s.name AS SchemaName,
t.name AS TableName,
t.lock_escalation_desc COLLATE SQL_Latin1_General_CP1_CI_AS AS lock_escalation_desc,
t.uses_ansi_nulls,
t.create_date AS CreateDate,
t.modify_date AS ModifyDate
FROM sys.schemas s (NOLOCK)
INNER JOIN sys.tables t (NOLOCK) ON s.SCHEMA_ID = t.SCHEMA_ID
WHERE t.is_ms_shipped = 0),
--#region TableScript
TableScript
AS (SELECT t.OBJECT_ID,
t.FQN,
t.SchemaName,
t.TableName,
t.TableName AS ObjectName,
'U' AS TYPE,
DropScript,
CreateScript,
CreateDate,
ModifyDate
FROM Tables t
INNER JOIN sys.indexes AS ix WITH (NOLOCK) ON t.OBJECT_ID = ix.OBJECT_ID
AND ix.index_id <= 1
INNER JOIN sys.data_spaces AS ds WITH (NOLOCK) ON ds.data_space_id = ix.data_space_id
LEFT OUTER JOIN sys.partitions AS pr WITH (NOLOCK) ON pr.OBJECT_ID = ix.OBJECT_ID
AND pr.index_id = ix.index_id
AND pr.partition_number = 1
LEFT OUTER JOIN sys.index_columns AS ic WITH (NOLOCK) ON ic.OBJECT_ID = ix.OBJECT_ID
AND ic.index_id = ix.index_id
AND ic.partition_ordinal = 1
LEFT OUTER JOIN sys.columns AS pc WITH (NOLOCK) ON pc.OBJECT_ID = ic.OBJECT_ID
AND pc.column_id = ic.column_id
CROSS APPLY (SELECT ')
' + CASE ds.type
WHEN 'FG' THEN 'ON ' + QUOTENAME(ds.NAME)
WHEN 'PS' THEN 'ON ' + QUOTENAME(ds.NAME) + '(' + QUOTENAME(pc.NAME) + ')'
WHEN 'FD' THEN ''
ELSE ''
END + CASE pr.data_compression
WHEN 0 THEN ''
WHEN 1 THEN ' WITH (DATA_COMPRESSION = ROW)'
WHEN 2 THEN ' WITH (DATA_COMPRESSION = PAGE)'
ELSE ''
END + CASE WHEN lock_escalation_desc <> 'TABLE' THEN '
ALTER TABLE ' + FQN + ' SET (LOCK_ESCALATION = ' + lock_escalation_desc + ')
' ELSE '
'
END AS AfterScript) ss
CROSS APPLY (SELECT Util.dbo.StringConcat(ColumnDef, ',
') AS ColumnDef
FROM (SELECT QUOTENAME(co.NAME) + ' ' + CASE WHEN cc.column_id IS NOT NULL
THEN 'AS ' + cc.definition + CASE WHEN cc.is_persisted = 1
THEN ' PERSISTED'
+ CASE WHEN co.is_nullable = 0
THEN ' NOT NULL'
ELSE ' NULL'
END
ELSE ''
END
ELSE CASE WHEN ts.name IN ('char', 'varchar')
THEN ts.name + '(' + CASE WHEN co.max_length = -1 THEN 'MAX'
ELSE CAST(co.max_length AS VARCHAR)
END + ')'
+ CASE WHEN co.collation_name COLLATE SQL_Latin1_General_CP1_CI_AS <> db.collation_name COLLATE SQL_Latin1_General_CP1_CI_AS
THEN ' COLLATE ' + co.collation_name COLLATE SQL_Latin1_General_CP1_CI_AS
ELSE ''
END
WHEN ts.name IN ('nchar', 'nvarchar')
THEN ts.name + '(' + CASE WHEN co.max_length = -1 THEN 'MAX'
ELSE CAST(co.max_length / 2 AS VARCHAR)
END + ')'
+ CASE WHEN co.collation_name COLLATE SQL_Latin1_General_CP1_CI_AS <> db.collation_name COLLATE SQL_Latin1_General_CP1_CI_AS
THEN ' COLLATE ' + co.collation_name COLLATE SQL_Latin1_General_CP1_CI_AS
ELSE ''
END
WHEN ts.name IN ('binary', 'varbinary')
THEN ts.name + '(' + CASE WHEN co.max_length = -1 THEN 'MAX'
ELSE CAST(co.max_length AS VARCHAR)
END + ')'
WHEN ts.name IN ('bigint', 'int', 'smallint', 'tinyint') THEN ts.name
WHEN ts.name IN ('datetime2', 'time', 'datetimeoffset')
THEN ts.name + '(' + CAST(co.scale AS VARCHAR) + ')'
WHEN ts.name IN ('numeric', 'decimal')
THEN ts.name + '(' + CAST(co.PRECISION AS VARCHAR) + ', '
+ CAST(co.scale AS VARCHAR) + ')'
ELSE ts.name
END + CASE WHEN co.is_identity = 1 THEN ' IDENTITY'
ELSE ''
END + ' ' + CASE WHEN co.is_nullable = 1 THEN 'NULL'
ELSE 'NOT NULL'
END
END AS ColumnDef
FROM sys.columns AS co WITH (NOLOCK)
INNER JOIN sys.types AS ts WITH (NOLOCK) ON ts.user_type_id = co.user_type_id
INNER JOIN sys.databases AS db WITH (NOLOCK) ON db.database_id = DB_ID()
LEFT OUTER JOIN sys.indexes ix (NOLOCK) ON ix.OBJECT_ID = co.OBJECT_ID
AND ix.is_primary_key = 1
LEFT OUTER JOIN sys.index_columns ic (NOLOCK) ON ic.OBJECT_ID = ix.OBJECT_ID
AND ic.index_id = ix.index_id
AND co.column_id = ic.column_id
LEFT OUTER JOIN sys.computed_columns AS cc (NOLOCK) ON co.is_computed = 1
AND co.OBJECT_ID = cc.OBJECT_ID
AND co.column_id = cc.column_id
WHERE co.OBJECT_ID = t.OBJECT_ID) k) k
CROSS APPLY (SELECT 'IF OBJECT_ID(''' + FQN + ''') IS NOT NULL DROP TABLE ' + FQN AS DropScript,
'CREATE TABLE ' + FQN + ' (
' + ColumnDef + AfterScript AS CreateScript) cs)--#endregion
, Defaults
AS (SELECT t.OBJECT_ID,
QUOTENAME(t.SchemaName) + '.' + QUOTENAME(dc.name) AS FQN,
t.SchemaName,
t.TableName,
dc.name AS ObjectName,
'D' AS TYPE,
DropScript,
CreateScript,
dc.create_date AS CreateDate,
dc.modify_date AS ModifyDate
FROM Tables t
INNER JOIN sys.default_constraints AS dc (NOLOCK) ON dc.parent_object_id = t.OBJECT_ID
INNER JOIN sys.columns c (NOLOCK) ON c.OBJECT_ID = t.OBJECT_ID
AND dc.parent_column_id = c.column_id
CROSS APPLY (SELECT 'IF OBJECT_ID(''' + QUOTENAME(t.SchemaName) + '.' + QUOTENAME(dc.name) + ''') IS NOT NULL ALTER TABLE ' + FQN
+ ' DROP CONSTRAINT ' + QUOTENAME(dc.name) AS DropScript,
'ALTER TABLE ' + FQN + ' ADD CONSTRAINT ' + QUOTENAME(dc.name) + ' DEFAULT ' + definition + ' FOR ' + QUOTENAME(c.name) AS CreateScript) kk),
--#region Indexes
Indexes
AS (SELECT t.OBJECT_ID,
t.SchemaName,
t.TableName,
CASE WHEN is_primary_key = 1
OR is_unique_constraint = 1 THEN QUOTENAME(t.SchemaName) + '.' + QUOTENAME(ix.name)
ELSE QUOTENAME(ix.name)
END AS FQN,
ix.name AS ObjectName,
ix.index_id AS IndexId,
CASE WHEN is_primary_key = 1 THEN 'PK'
WHEN is_unique_constraint = 1 THEN 'UQ'
ELSE 'I'
END AS TYPE,
DropScript,
CreateScript,
t.CreateDate,
t.ModifyDate
FROM Tables t
INNER JOIN sys.indexes AS ix WITH (NOLOCK) ON t.OBJECT_ID = ix.OBJECT_ID
AND ix.index_id > 0
CROSS APPLY (SELECT QUOTENAME (ix.name) AS IndexName) f
LEFT OUTER JOIN sys.data_spaces AS ds WITH (NOLOCK) ON ds.data_space_id = ix.data_space_id
LEFT OUTER JOIN sys.partitions AS pr WITH (NOLOCK) ON pr.OBJECT_ID = ix.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 99999999
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
CROSS APPLY (SELECT ISNULL(' WITH (' + NULLIF(LTRIM(RTRIM(SUBSTRING(ISNULL(', ' + dc.DataCompression, '') + ', SORT_IN_TEMPDB = ON'
+ 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) IO
CROSS APPLY (SELECT CASE WHEN is_primary_key = 1
OR is_unique_constraint = 1
THEN 'IF OBJECT_ID(''' + QUOTENAME(SchemaName) + '.' + IndexName + ''') IS NOT NULL ALTER TABLE ' + FQN
+ ' DROP CONSTRAINT ' + IndexName
ELSE 'IF EXISTS(SELECT * FROM sys.indexes (NOLOCK) WHERE object_id = OBJECT_ID(''' + FQN + ''') AND name = '''
+ ix.name + ''') DROP INDEX ' + IndexName + ' ON ' + FQN
END AS DropScript,
CASE WHEN has_filter = 1 THEN 'SET ANSI_NULLS ' + CASE WHEN uses_ansi_nulls = 1 THEN 'ON'
ELSE 'OFF'
END + '
' ELSE ''
END + CASE WHEN is_primary_key = 1
OR is_unique_constraint = 1
THEN 'ALTER TABLE ' + FQN + ' ADD CONSTRAINT ' + IndexName + ' '
+ CASE WHEN is_primary_key = 1 THEN 'PRIMARY KEY '
ELSE 'UNIQUE '
END + ix.type_desc COLLATE SQL_Latin1_General_CP1_CI_AS + ' (' + KeyColumns + ')' + IndexOptions + ' '
+ CASE ds.type COLLATE SQL_Latin1_General_CP1_CI_AS
WHEN 'FG' THEN 'ON ' + QUOTENAME(ds.name COLLATE SQL_Latin1_General_CP1_CI_AS)
WHEN 'PS'
THEN 'ON ' + QUOTENAME(ds.name COLLATE SQL_Latin1_General_CP1_CI_AS) + '(' + PartitionedColumn + ')'
WHEN 'FD' THEN ''
ELSE ''
END
ELSE 'CREATE ' + CASE WHEN is_unique = 1 THEN 'UNIQUE '
ELSE ''
END + ix.type_desc COLLATE SQL_Latin1_General_CP1_CI_AS + ' INDEX ' + IndexName + ' ON ' + FQN
+ ' (' + KeyColumns + ')' + ISNULL(' INCLUDE (' + IncludeColumns + ')', '') + ISNULL(' WHERE '
+ filter_definition, '')
+ IndexOptions + ' ' + CASE ds.type COLLATE SQL_Latin1_General_CP1_CI_AS
WHEN 'FG' THEN 'ON ' + QUOTENAME(ds.name COLLATE SQL_Latin1_General_CP1_CI_AS) + ''
WHEN 'PS'
THEN 'ON ' + QUOTENAME(ds.name COLLATE SQL_Latin1_General_CP1_CI_AS) + '('
+ PartitionedColumn + ')'
WHEN 'FD' THEN ''
ELSE ''
END
END AS CreateScript) cs),
--#endregion
--#region Check Constraints
Checks
AS (SELECT t.OBJECT_ID,
QUOTENAME(t.SchemaName) + '.' + QUOTENAME(ck.name) AS FQN,
t.SchemaName,
t.TableName,
ck.name AS ObjectName,
'C' AS TYPE,
DropScript,
CreateScript,
ck.create_date AS CreateDate,
ck.modify_date AS ModifyDate
FROM Tables t
INNER JOIN sys.check_constraints AS ck (NOLOCK) ON ck.parent_object_id = t.OBJECT_ID
CROSS APPLY (SELECT 'IF OBJECT_ID(''' + QUOTENAME(t.SchemaName) + '.' + QUOTENAME(ck.name) + ''') IS NOT NULL ALTER TABLE ' + FQN
+ ' DROP CONSTRAINT ' + QUOTENAME(ck.name) AS DropScript,
'ALTER TABLE ' + FQN + ' ' + CASE WHEN is_disabled = 1 THEN 'WITH NOCHECK '
ELSE ''
END + 'ADD CONSTRAINT ' + QUOTENAME(ck.name) + ' CHECK ' + definition
+ CASE WHEN is_disabled = 1 THEN '
ALTER TABLE ' + FQN + ' NOCHECK CONSTRAINT ' + QUOTENAME(ck.name)
ELSE ''
END AS CreateScript) kk),
--#endregion
--#region ForeignKeys
ForeignKeys
AS (SELECT t.OBJECT_ID,
QUOTENAME(t.SchemaName) + '.' + QUOTENAME(fk.name) AS FQN,
t.SchemaName,
t.TableName,
fk.name AS ObjectName,
'F' AS TYPE,
DropScript,
CreateScript,
fk.create_date AS CreateDate,
fk.modify_date AS ModifyDate
FROM Tables t
INNER JOIN sys.foreign_keys AS fk (NOLOCK) ON fk.parent_object_id = t.OBJECT_ID
INNER JOIN sys.tables AS rt (NOLOCK) ON rt.OBJECT_ID = fk.referenced_object_id
INNER JOIN sys.schemas AS rs (NOLOCK) ON rs.SCHEMA_ID = rt.SCHEMA_ID
CROSS APPLY (SELECT Util.dbo.StringConcat(QUOTENAME(ParentColumn), ', ') AS ParentColumns,
Util.dbo.StringConcat(QUOTENAME(ReferencedColumn), ', ') AS ReferencedColumns
FROM (SELECT TOP 999999
pc.name AS ParentColumn,
rc.name AS ReferencedColumn
FROM sys.foreign_key_columns fc (NOLOCK)
INNER JOIN sys.columns pc (NOLOCK) ON pc.OBJECT_ID = fk.parent_object_id
AND fc.parent_column_id = pc.column_id
INNER JOIN sys.columns rc (NOLOCK) ON rc.OBJECT_ID = fk.referenced_object_id
AND fc.referenced_column_id = rc.column_id
WHERE fc.constraint_object_id = fk.OBJECT_ID
ORDER BY fc.constraint_column_id) K) fc
CROSS APPLY (SELECT 'IF OBJECT_ID(''' + QUOTENAME(t.SchemaName) + '.' + QUOTENAME(fk.name) + ''') IS NOT NULL ALTER TABLE ' + FQN
+ ' DROP CONSTRAINT ' + QUOTENAME(fk.name) AS DropScript,
'ALTER TABLE ' + FQN + CASE WHEN is_disabled = 1 THEN ' WITH NOCHECK'
ELSE ''
END + ' ADD CONSTRAINT ' + QUOTENAME(fk.name) + ' FOREIGN KEY (' + ParentColumns + ') REFERENCES '
+ QUOTENAME(rs.name) + '.' + QUOTENAME(rt.name) + ' (' + ReferencedColumns + ')'
+ CASE WHEN delete_referential_action > 0
THEN ' ON DELETE ' + delete_referential_action_desc COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS
ELSE ''
END + CASE WHEN update_referential_action > 0
THEN ' ON UPDATE ' + update_referential_action_desc COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS
ELSE ''
END + CASE WHEN is_not_for_replication = 1 THEN ' NOT FOR REPLICATION'
ELSE ''
END + CASE WHEN is_disabled = 1 THEN '
ALTER TABLE ' + FQN + ' NOCHECK CONSTRAINT ' + QUOTENAME(fk.name) + '
' ELSE ''
END AS CreateScript) sc)
--#endregion
SELECT TYPE,
FQN,
SchemaName,
TableName,
ObjectName,
DropScript,
CreateScript,
0 AS IndexId,
CreateDate,
ModifyDate,
OBJECT_ID AS ObjectId
FROM TableScript
UNION ALL
SELECT TYPE,
FQN,
SchemaName,
TableName,
ObjectName,
DropScript,
CreateScript,
0 AS IndexId,
CreateDate,
ModifyDate,
OBJECT_ID AS ObjectId
FROM Defaults
UNION ALL
SELECT TYPE,
FQN,
SchemaName,
TableName,
ObjectName,
DropScript,
CreateScript,
IndexId,
CreateDate,
ModifyDate,
OBJECT_ID AS ObjectId
FROM Indexes
UNION ALL
SELECT TYPE,
FQN,
SchemaName,
TableName,
ObjectName,
DropScript,
CreateScript,
0 AS IndexId,
CreateDate,
ModifyDate,
OBJECT_ID AS ObjectId
FROM Checks
UNION ALL
SELECT TYPE,
FQN,
SchemaName,
TableName,
ObjectName,
DropScript,
CreateScript,
0 AS IndexId,
CreateDate,
ModifyDate,
OBJECT_ID AS ObjectId
FROM ForeignKeys