Dynamic SQL - Drop Keys Truncate Tables and Add Keys

So I need to do the same routine when deploying to a test environment or a stage database when I add columns in the middle of a table or change the nullability of a column. Getting the column definition for a constraint can be a pain, so I wanted to record this here for reuse (thanks to Pinal Dave for keeping the old keys sys tables query online).


USE <DATABASE>


-------------------- BEGIN - Initialize System State Tables -------------------

DECLARE

@name VARCHAR(128),

@schema VARCHAR(128),

@tableSchema VARCHAR(128),

@tableName VARCHAR(128),

@sqlCommand NVARCHAR(1000),

@Rows INT,

@i INT ;

DECLARE @sysFKTable TABLE (RowID INT IDENTITY(1,1), ConstraintName sysname, SchemaName sysname, TableName sysname, ColumnName sysname, ReferenceSchemaName sysname, ReferenceTableName sysname, ReferenceColumnName sysname)

DECLARE @schemaTbl TABLE(RowID INT IDENTITY(1,1), SchemaName VARCHAR(128), TableName VARCHAR(128), IndexName VARCHAR(128));

DECLARE @t TABLE(RowID INT IDENTITY(1,1), SchemaName VARCHAR(128), ObjectName VARCHAR(128));

DECLARE @t1 TABLE(RowID INT IDENTITY(1,1), SchemaName VARCHAR(128), ObjectName VARCHAR(128));

PRINT 'Load @sysFKTable'

INSERT INTO @sysFKTable

SELECT f.name AS ConstraintName,

SCHEMA_NAME(f.SCHEMA_ID) SchemaName,

OBJECT_NAME(f.parent_object_id) AS TableName,

COL_NAME(fc.parent_object_id,fc.parent_column_id) AS ColumnName,

SCHEMA_NAME(o.SCHEMA_ID) ReferenceSchemaName,

OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,

COL_NAME(fc.referenced_object_id,fc.referenced_column_id) AS ReferenceColumnName

FROM sys.foreign_keys AS f

JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id

JOIN sys.objects AS o ON o.OBJECT_ID = fc.referenced_object_id



-- ORDER BY tc.TABLE_SCHEMA, tc.TABLE_NAME, tc.CONSTRAINT_SCHEMA, tc.CONSTRAINT_NAME

PRINT 'Load @schemaTbl'

INSERT INTO @schemaTbl(SchemaName, TableName, IndexName)

select object_schema_name(object_id), object_name(object_id), name

from sys.indexes

where type > 0 AND object_schema_name(object_id) != 'sys'

PRINT 'Load @t for table trunc'

INSERT INTO @t(SchemaName, ObjectName)

SELECT

tc.TABLE_SCHEMA,

tc.TABLE_NAME

FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc

WHERE (tc.CONSTRAINT_CATALOG = DB_NAME()) AND (tc.CONSTRAINT_TYPE = 'FOREIGN KEY')

ORDER BY SUBSTRING(tc.CONSTRAINT_NAME,PATINDEX('%o[_]%',tc.CONSTRAINT_NAME)+2,100) DESC

PRINT 'Load @t1 for table trunc'

INSERT INTO @t1

SELECT t.TABLE_SCHEMA, t.TABLE_NAME

FROM INFORMATION_SCHEMA.TABLES t

WHERE t.TABLE_TYPE = 'BASE TABLE'

ORDER BY t.TABLE_SCHEMA, t.TABLE_NAME DESC





PRINT 'This replaces merge statement as this targets sql server 2005'

INSERT INTO @t

SELECT

t1.SchemaName,

t1.ObjectName

FROM @t1 t1

WHERE

t1.ObjectName NOT IN (SELECT t.ObjectName FROM @t t)



-------------------- END - Initialize System State Tables -------------------

-------------------- BEGIN - Drop FKs ---------------------------------------

SET @Rows = (SELECT COUNT(RowID) FROM @sysFKTable)

SET @i = 1

WHILE (@i <= @Rows)



BEGIN

SELECT @sqlCommand = 'ALTER TABLE [' + t.SchemaName + '].[' + t.TableName + '] DROP CONSTRAINT [' + t.ConstraintName + '];',

@name = t.ConstraintName,

@tableSchema = t.SchemaName,

@tableName = t.TableName

FROM @SysFKTable t WHERE RowID = @i;

EXEC sp_executesql @sqlCommand;

PRINT 'DROP CONSTRAINT: ' + @name + ' on [' + @tableSchema + '].[' + @tableName + ']';

SET @i = @i + 1;



END



--------------------------- END - Drop FKs---------------------------------

--------------------------- BEGIN - TRUNCATE TABLES------------------------

SET @Rows = (SELECT COUNT(RowID) FROM @t)

SET @i = 1;

WHILE (@i <= @Rows)



BEGIN

SELECT @sqlCommand = 'TRUNCATE TABLE [' + t.SchemaName + '].[' + t.ObjectName + '];', @schema = t.SchemaName, @name = t.ObjectName FROM @t t WHERE RowID = @i;

EXEC sp_executesql @sqlCommand;

PRINT 'TRUNCATE TABLE: [' + @schema + '].[' + @name + ']';

SET @i = @i + 1;



END

------------------------- END - Truncate Tables ------------------------------

------------------------- BEGIN - ADD BACK CONSTRAINTS -----------------------

SET @Rows = (SELECT COUNT(RowID) FROM @sysFKTable)

SET @i = 1

WHILE (@i <= @Rows)



BEGIN

BEGIN TRY

SELECT @sqlCommand = 'ALTER TABLE [' + SchemaName + '].[' + TableName + '] WITH CHECK ADD CONSTRAINT [' + ConstraintName

+ '] FOREIGN KEY([' + ColumnName + ']) REFERENCES [' + ReferenceSchemaName + '].[' + ReferenceTableName

+ '] ([' + ReferenceColumnName + '])',

@name = t.ConstraintName,

@tableSchema = t.SchemaName,

@tableName = t.TableName

FROM @SysFKTable t WHERE RowID = @i;

EXEC sp_executesql @sqlCommand;

PRINT 'ADD CONSTRAINT: ' + @name + ' on [' + @tableSchema + '].[' + @tableName + ']';

SET @i = @i + 1;

END TRY

BEGIN CATCH

PRINT 'Error in creating constraints'

END CATCH



END

------------------------- END - Add FKs ----------------------------------------

------------------------- BEGIN - INDEX MGMT -----------------------------------

SET @Rows = (SELECT COUNT(RowID) FROM @schemaTbl)

SET @i = 1;

WHILE (@i <= @Rows)

BEGIN

 SELECT @sqlCommand = 'ALTER INDEX ' + t.IndexName + ' ON ' + t.SchemaName + '.' + t.TableName + ' REBUILD;',

@schema = t.SchemaName, @name = t.IndexName, @tableSchema = t.SchemaName, @tableName = t.TableName

FROM @schemaTbl t WHERE RowID = @i;

EXEC sp_executesql @sqlCommand;

PRINT 'REBUILD INDEX: ' + @name + ' on [' + @tableSchema + '].[' + @tableName + ']';

SET @i = @i + 1;

END

GO ------------------------- END ------------------------------------------------