SET QUOTED_IDENTIFIER ON;
SET ANSI_NULLS ON;
GO
-- =============================================
-- Author: Yawahang Rai
-- Create date: 12/18/2018
-- Description: Stored Procedure To Truncate all data
-- =============================================
ALTER PROCEDURE dbo.UtlResetDatabase
AS
BEGIN
SET NOCOUNT ON;
DECLARE @i INT ,
@TableName VARCHAR (80) ,
@ColumnName VARCHAR (80) ,
@ReferencedTableName VARCHAR (80) ,
@ReferencedColumnName VARCHAR (80) ,
@ConstraintName VARCHAR (250) ,
@CreateStatement VARCHAR (MAX) ,
@DropStatement VARCHAR (MAX) ,
@CreateStatementTemp VARCHAR (MAX) ,
@DropStatementTemp VARCHAR (MAX) ,
@Statement VARCHAR (MAX);
SET @i = 1;
SET @CreateStatement = 'ALTER TABLE [dbo].[<tablename>] WITH NOCHECK ADD
CONSTRAINT [<constraintname>] FOREIGN KEY([<column>]) REFERENCES [dbo].[<reftable>]
([<refcolumn>])';
SET @DropStatement = 'ALTER TABLE [dbo].[<tablename>] DROP CONSTRAINT
[<constraintname>]';
PRINT '************************************** Backing up Foreign Key
Definitions';
CREATE TABLE #FKs
( ID INT NOT NULL IDENTITY (1, 1) PRIMARY KEY ,
ConstraintName VARCHAR (MAX) NULL ,
TableName VARCHAR (MAX) NULL ,
ColumnName VARCHAR (MAX) NULL ,
ReferencedTableName VARCHAR (MAX) NULL ,
ReferencedColumnName VARCHAR (MAX) NULL );
INSERT INTO #FKs ( ConstraintName ,
TableName ,
ColumnName ,
ReferencedTableName ,
ReferencedColumnName )
SELECT OBJECT_NAME (fk.constraint_object_id) AS ConstraintName ,
OBJECT_NAME (fk.parent_object_id) ,
clm1.name ,
OBJECT_NAME (fk.referenced_object_id) ,
clm2.name
FROM sys.foreign_key_columns fk
JOIN sys.columns clm1 ON fk.parent_column_id =
clm1.column_id
AND fk.parent_object_id =
clm1.object_id
JOIN sys.columns clm2 ON fk.referenced_column_id =
clm2.column_id
AND fk.referenced_object_id =
clm2.object_id
WHERE EXISTS ( SELECT 1
FROM INFORMATION_SCHEMA.TABLES AS t
WHERE t.TABLE_TYPE = 'BASE TABLE'
AND t.TABLE_NAME NOT IN ( 'sysdiagrams' )
AND OBJECT_NAME (fk.referenced_object_id) =
t.TABLE_NAME )
ORDER BY OBJECT_NAME (fk.parent_object_id);
PRINT '************************************** Finished Backing up Foreign Key
Definitions';
PRINT '************************************** Generating Drop & Re-Generate
Fks and Truncate Table query';
CREATE TABLE #Internal_FK_Definition_Storage
( ID INT NOT NULL IDENTITY (1, 1) PRIMARY KEY ,
FK_Name VARCHAR (250) NULL ,
FK_CreationStatement VARCHAR (MAX) NULL ,
FK_DestructionStatement VARCHAR (MAX) NULL ,
Table_TruncationStatement VARCHAR (MAX) NULL );
WHILE ( @i <= ( SELECT MAX (#FKs.ID)
FROM #FKs ))
BEGIN
SELECT @ConstraintName = #FKs.ConstraintName
FROM #FKs
WHERE #FKs.ID = @i;
SELECT @TableName = #FKs.TableName
FROM #FKs
WHERE #FKs.ID = @i;
SELECT @ColumnName = #FKs.ColumnName
FROM #FKs
WHERE #FKs.ID = @i;
SELECT @ReferencedTableName = #FKs.ReferencedTableName
FROM #FKs
WHERE #FKs.ID = @i;
SELECT @ReferencedColumnName = #FKs.ReferencedColumnName
FROM #FKs
WHERE #FKs.ID = @i;
SELECT @DropStatementTemp = REPLACE (
REPLACE (@DropStatement,
'<tablename>', @TableName) ,
'<constraintname>' ,
@ConstraintName);
SELECT @CreateStatementTemp = REPLACE (
REPLACE (
REPLACE (
REPLACE (
REPLACE (
@CreateStatement, '<tablename>', @TableName) ,
'<column>' ,
@ColumnName) ,
'<constraintname>' ,
@ConstraintName) ,
'<reftable>' ,
@ReferencedTableName) ,
'<refcolumn>' ,
@ReferencedColumnName);
INSERT INTO #Internal_FK_Definition_Storage ( FK_Name ,
FK_CreationStatement ,
FK_DestructionStatement )
SELECT @ConstraintName ,
@CreateStatementTemp ,
@DropStatementTemp;
SET @i = @i + 1;
END;
PRINT '************************************** Finished Generating Drop &
Re-Generate Fks and Truncate Table query';
PRINT '************************************** Dropping Foreign Keys';
SET @i = 1;
WHILE ( @i <= ( SELECT MAX (t.ID)
FROM #Internal_FK_Definition_Storage t ))
BEGIN
SELECT @ConstraintName = tt.FK_Name
FROM #Internal_FK_Definition_Storage tt
WHERE tt.ID = @i;
SELECT @Statement = ttt.FK_DestructionStatement
FROM #Internal_FK_Definition_Storage ttt WITH ( NOLOCK )
WHERE ttt.ID = @i;
EXEC ( @Statement );
SET @i = @i + 1;
END;
PRINT '************************************** Finished Dropping Foreign Keys';
PRINT '************************************** Truncating Tables';
-- DBCC CHECKIDENT reset Identity
SELECT @Statement = STUFF (
( SELECT ' TRUNCATE TABLE dbo.[' + t.TABLE_NAME + '];
DBCC CHECKIDENT(''' + t.TABLE_NAME
+ ''', RESEED, 0); '
FROM INFORMATION_SCHEMA.TABLES AS t
WHERE t.TABLE_TYPE = 'BASE TABLE'
AND t.TABLE_NAME NOT IN ( 'sysdiagrams' )
ORDER BY t.TABLE_NAME
FOR XML PATH ('')) ,
1 ,
1 ,
'');
EXEC ( @Statement );
PRINT '************************************** Finished Truncating Tables';
PRINT '************************************** Re-Creating Foreign Keys';
SET @i = 1;
WHILE ( @i <= ( SELECT MAX (t.ID)
FROM #Internal_FK_Definition_Storage t ))
BEGIN
SELECT @ConstraintName = tt.FK_Name
FROM #Internal_FK_Definition_Storage tt
WHERE tt.ID = @i;
SELECT @Statement = ttt.FK_CreationStatement
FROM #Internal_FK_Definition_Storage ttt
WHERE ttt.ID = @i;
EXEC ( @Statement );
SET @i = @i + 1;
END;
PRINT '************************************** Finished Re-Creating
Foreign Keys';
DROP TABLE #FKs ,
#Internal_FK_Definition_Storage;
END;
GO
Comments
Post a Comment