USE [master]
GO
IF OBJECT_ID('dbo.sp_TableReplace') IS NULL
EXEC('CREATE PROCEDURE dbo.sp_TableReplace AS SELECT 1 AS ID')
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE dbo.sp_TableReplace
@Table SYSNAME = NULL,
@SQL VARCHAR(MAX) = NULL OUTPUT,
@PrintSQL BIT = 1,
@SortTableColumns BIT = 0,
@IncludeTriggerScript BIT = 1,
@IncludeInsert BIT = 1,
@FilePath VARCHAR(4000) = NULL /* Will write the generated sql to the path */
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @OBJECTID INT = OBJECT_ID(Util.dbo.TrimBothEnds(@Table))
IF @OBJECTID IS NULL
BEGIN
EXEC master.dbo.sp_ExecTemplate
@ObjectName = 'sp_TableReplace',
@PrintDefault = 0,
@DeclareReturn = 0,
@PrintReturnLine = 0,
@DoNotDeclareVar = 1
RETURN 30
END
DECLARE @DropRenameSQL VARCHAR(MAX),
@CreateRed VARCHAR(MAX),
@DropRed VARCHAR(MAX),
@CreateRing VARCHAR(MAX),
@DropRing VARCHAR(MAX),
@DropDefaultConstraint VARCHAR(MAX),
@CreateDefaultConstraint VARCHAR(MAX),
@TriggerScript VARCHAR(MAX),
@CreateTable VARCHAR(MAX),
@CreateIndex VARCHAR(MAX),
@DropSchemaBound VARCHAR(MAX),
@CreateSchemaBound VARCHAR(MAX),
@AddNamePrefix VARCHAR(256) = LEFT(REPLACE(CAST(NEWID() AS VARCHAR(128)), '-', ''), 8) + '_' ;
WITH MergeList
AS (SELECT TOP 999999
s.name AS SchemaName,
t.Name AS TableName,
CASE WHEN c.is_computed = 1
OR c.is_rowguidcol = 1 THEN NULL
ELSE QUOTENAME(c.name)
END AS InsertList,
CASE WHEN c.is_computed = 1
OR c.is_rowguidcol = 1 THEN NULL
ELSE 't1.' + QUOTENAME(c.name)
END AS SelectList,
CAST(c.is_identity AS INT) AS is_identity
FROM sys.schemas s (NOLOCK)
INNER JOIN sys.tables t (NOLOCK) ON s.SCHEMA_ID = t.SCHEMA_ID
INNER JOIN sys.columns c (NOLOCK) ON c.OBJECT_ID = t.OBJECT_ID
WHERE t.is_ms_shipped = 0
AND t.OBJECT_ID = @OBJECTID
ORDER BY 1,
c.column_id)
SELECT @SQL = (SELECT CASE WHEN @IncludeInsert = 0 THEN '/*
' ELSE ''
END + CASE WHEN SUM(is_identity) > 0
THEN 'SET IDENTITY_INSERT ' + QUOTENAME(SchemaName) + '.' + QUOTENAME(@AddNamePrefix + TableName) + ' ON
' ELSE ''
END + '
INSERT ' + QUOTENAME(SchemaName) + '.' + QUOTENAME(@AddNamePrefix + TableName) + ' WITH (TABLOCK)
(' + Util.dbo.StringConcat(InsertList, ',
') + ')
SELECT
' + Util.dbo.StringConcat(SelectList, ',
') + '
FROM ' + QUOTENAME(SchemaName) + '.' + QUOTENAME(TableName) + ' t1
' + CASE WHEN SUM(is_identity) > 0 THEN 'SET IDENTITY_INSERT ' + QUOTENAME(SchemaName) + '.' + QUOTENAME(@AddNamePrefix + TableName) + ' OFF
' ELSE ''
END + CASE WHEN @IncludeInsert = 0 THEN '
*/
' ELSE ''
END + '
'
FROM MergeList
GROUP BY SchemaName,
TableName),
@DropRenameSQL = (SELECT DISTINCT
'-- !!!!!!!!!!!!!!!!!!!!!!!!!!! WATCH OUT DROPPING THE TABLE !!!!!!!!!!!!!!!!!!!!!!!!!!!
DROP TABLE ' + QUOTENAME(SchemaName) + '.' + QUOTENAME(TableName) + '
EXEC SP_RENAME ''' + QUOTENAME(SchemaName) + '.' + QUOTENAME(@AddNamePrefix + TableName) + ''', ''' + TableName + '''
'
FROM MergeList)
IF @IncludeTriggerScript = 1
SELECT @TriggerScript = Util.dbo.StringConcat('IF OBJECT_ID(''' + QUOTENAME(sc.name) + '.' + QUOTENAME(t.name) + ''') IS NULL
EXEC(''' + REPLACE(s.definition, '''', '''''') + ''')', '
GO')
FROM sys.triggers t (NOLOCK)
INNER JOIN sys.objects o (NOLOCK) ON t.parent_id = o.OBJECT_ID
INNER JOIN sys.schemas sc (NOLOCK) ON sc.SCHEMA_ID = o.SCHEMA_ID
INNER JOIN sys.all_sql_modules s (NOLOCK) ON t.OBJECT_ID = s.OBJECT_ID
WHERE t.parent_id = @OBJECTID
--#region Script Schema Bound
IF OBJECT_ID('TEMPDB..#ObjectList') IS NOT NULL
DROP TABLE #ObjectList
CREATE TABLE #ObjectList (SchemaName VARCHAR(256) NULL,
Name VARCHAR(256) NULL)
INSERT #ObjectList
(SchemaName,
Name)
SELECT s.name AS SchemaName,
o.name
FROM sys.sql_expression_dependencies ed (NOLOCK)
INNER JOIN sys.objects o (NOLOCK) ON ed.referencing_id = o.OBJECT_ID
INNER JOIN sys.schemas s (NOLOCK) ON s.SCHEMA_ID = o.SCHEMA_ID
WHERE referenced_id = @OBJECTID
AND is_schema_bound_reference = 1
AND referencing_class = 1
AND referenced_minor_id = 0
AND o.type IN ('V', 'FN', 'IF', 'TF', 'P')
IF @@ROWCOUNT > 0
BEGIN
EXEC dbo.sp_script
@DropView = 1,
@DropFunction = 1,
@DropProcedure = 1,
@UseGOBatchTerminator = 1,
@SQL = @DropSchemaBound OUTPUT
EXEC dbo.sp_script
@CreateView = 1,
@CreateFunction = 1,
@CreateProcedure = 1,
@CreateClusteredIndex = 1,
@CreateNonClusteredIndex = 1,
@GenerateIfNotExists = 1,
@UseGOBatchTerminator = 1,
@SQL = @CreateSchemaBound OUTPUT
END
IF OBJECT_ID('TEMPDB..#ObjectList') IS NOT NULL
DROP TABLE #ObjectList
--#endregion
EXEC sp_FK_Script
@Table,
@CreateRed = @CreateRed OUTPUT,
@DropRed = @DropRed OUTPUT,
@CreateRing = @CreateRing OUTPUT,
@DropRing = @DropRing OUTPUT,
@PrintSQL = 0
EXEC sp_script
@ObjectName = @Table,
@DropDefaultConstraints = 1,
@UseGOBatchTerminator = 1,
@SQL = @DropDefaultConstraint OUTPUT
EXEC sp_script
@ObjectName = @Table,
@CreateDefaultConstraints = 1,
@UseGOBatchTerminator = 1,
@SQL = @CreateDefaultConstraint OUTPUT
EXEC sp_script
@ObjectName = @Table,
@AddNamePrefix = @AddNamePrefix,
@NewDataCompression = '',
@DropTable = 1,
@CreateTable = 1,
@SortTableColumns = @SortTableColumns,
@UseGOBatchTerminator = 1,
@SQL = @CreateTable OUTPUT
EXEC sp_script
@ObjectName = @Table,
@CreatePrimaryKey = 1,
@CreateUniqueKey = 1,
@CreateIndex = 1,
@CreateCheckConstraint = 1,
@UseGOBatchTerminator = 1,
@SQL = @CreateIndex OUTPUT
SET @SQL = 'BEGIN TRANSACTION
GO
' + ISNULL(@DropSchemaBound, '') + '
' + ISNULL(@DropRed + '
', '') + '-- NO FOREIGN KEYS REFERENCING THIS TABLE
GO
' + ISNULL(@DropDefaultConstraint, '') + '
' + @CreateTable + '
BEGIN TRY
' + @SQL + @DropRenameSQL + 'END TRY
BEGIN CATCH
EXEC sp_ErrorHandler @RaiseError = 1, @PrintError = 1
ROLLBACK TRANSACTION
END CATCH
GO
' + ISNULL(@CreateDefaultConstraint, '') + '
' + ISNULL(@CreateIndex, '') + ISNULL(@CreateRed, '') + ISNULL(@CreateRing, '') + ISNULL(@CreateSchemaBound, '') + '
GO
-- !!!!!!!!!!!!!!!!!!!!!!!!!!! COMMIT OR ROLLBACK !!!!!!!!!!!!!!!!!!!!!!!!!!!
-- COMMIT TRANSACTION
-- ROLLBACK TRANSACTION
GO
'
IF @PrintSQL = 1
BEGIN
EXEC Util.dbo.PrintLargeText
@SQL
EXEC Util.dbo.PrintLargeText
@TriggerScript
END
SET @SQL = @SQL + ISNULL(@TriggerScript, '')
IF @FilePath <> ''
SELECT ReturnVal,
MESSAGE,
'Saved to ' + @FilePath AS Info
FROM Util.FS.AppendAllTextToFile(@FilePath, @SQL, 1)
GO
EXEC sys.sp_ms_marksystemobject
sp_TableReplace
GO