Truncating Table Data Was Made Easy – SQL Server

I have used 3 Procedures to make Truncate Table easy, along with truncating all the related tables for a given table.

— TruncateTable is the main proc to be used to see all tables that are getting truncated by not giving 2nd parameter and once you satisfy with the list of tables that are going to be affected then you can run the same proc by giving 2nd parameter as 0 (Zero).

CREATE PROCEDURE [dbo].[TruncateTable]
 @TABLE       VARCHAR(100),
 @NoTruncateOnlyShowRelations BIT = 1
AS
BEGIN

 DECLARE  @LEVEL INT

 SET @LEVEL = 1
 CREATE TABLE #FKKeysTable
 (PKTableName VARCHAR(100), PKColumnName varchar(100),
 FKTableName varchar(100), FKColumnName varchar(100), ForeignKey varchar(100), DROPCon BIT, CREATECon BIT, NESTLEVEL INT)
     
 IF @NoTruncateOnlyShowRelations = 0
 BEGIN
  EXEC TruncateTableRec @TABLE, @LEVEL
 END
 ELSE
 BEGIN
  EXEC ShowTruncateTableRec @TABLE, @LEVEL
 END

 SELECT
  *
 FROM
  #FKKeysTable
 ORDER BY
  NESTLEVEL
 
 
 DROP TABLE #FKKeysTable
END
GO
————————————–

CREATE PROCEDURE [dbo].[TruncateTableRec]
 @TABLE   VARCHAR(100),
 @LEVEL   INT
AS
BEGIN

 DECLARE @PKTableName varchar(255),
   @PKColumnName varchar(255),
   @FKTableName varchar(255),
   @FKColumnName varchar(255),
   @ForeignKey varchar(255),
   @SQL varchar(3000)
 
 INSERT INTO #FKKeysTable
  SELECT
   OBJECT_NAME (f.referenced_object_id) AS PKTableName,
   COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS PKColumnName, 
   OBJECT_NAME(f.parent_object_id) AS FKTableName,
   COL_NAME(fc.parent_object_id, fc.parent_column_id) AS FKColumnName,
   f.name AS ForeignKey,
   0,
   0,
   @LEVEL
  FROM
   sys.foreign_keys AS f
   INNER JOIN sys.foreign_key_columns AS fc ON fc.constraint_object_id = f.OBJECT_ID   
   LEFT OUTER JOIN #FKKeysTable AS fkt ON fkt.PKTableName = @TABLE
  where
   OBJECT_NAME (f.referenced_object_id) = ” + @TABLE + ”     
   AND (fkt.PKTableName IS NULL AND fkt.PKColumnName IS NULL AND fkt.FKTableName IS NULL
   AND fkt.FKColumnName IS NULL AND fkt.ForeignKey IS NULL)
  order by
   PKTableName
   

 SELECT TOP 1  @PKTableName = PKTableName, @PKColumnName = PKColumnName,
  @FKTableName = FKTableName, @FKColumnName = FKColumnName, @ForeignKey = ForeignKey
 FROM #FKKeysTable
 where DROPCon = 0 AND NESTLEVEL = @LEVEL
 
 WHILE (ISNULL(@ForeignKey, ”) <> ” )
 BEGIN
  PRINT @TABLE + ‘1’   
  
  SET @SQL = ‘IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N”[dbo].[‘+@ForeignKey+’]”) AND parent_object_id = OBJECT_ID(N”[dbo].[‘+@FKTableName+’]”)) ALTER TABLE [dbo].[‘+@FKTableName+’] DROP CONSTRAINT [‘+@ForeignKey+’]’
  
  EXEC (@SQL)
     
  UPDATE #FKKeysTable  SET DROPCon = 1 WHERE ForeignKey = @ForeignKey
  
  SET @ForeignKey = ”
  
  SELECT TOP 1  @PKTableName = PKTableName, @PKColumnName = PKColumnName,
   @FKTableName = FKTableName, @FKColumnName = FKColumnName, @ForeignKey = ForeignKey
  FROM #FKKeysTable
  where DROPCon = 0  AND NESTLEVEL = @LEVEL
 END
 
 PRINT @TABLE + ‘2’  
 SET @SQL = ‘TRUNCATE TABLE ‘ + @TABLE
 EXEC (@SQL)

 PRINT @TABLE + ‘3’  
 SELECT TOP 1  @PKTableName = PKTableName, @PKColumnName = PKColumnName,
  @FKTableName = FKTableName, @FKColumnName = FKColumnName, @ForeignKey = ForeignKey
 FROM #FKKeysTable
 where CREATECon = 0 AND NESTLEVEL = @LEVEL
 
 WHILE (ISNULL(@ForeignKey, ”) <> ” )
 BEGIN
  PRINT @TABLE + ‘4’
  
  SET @SQL = ‘DECLARE @LEVEL1 INT SET @LEVEL1 =’ + CAST(@LEVEL AS VARCHAR(10))+ ‘ SET @LEVEL1 = @LEVEL1 + 1 IF((SELECT COUNT(*) FROM ‘ + @FKTableName + ‘) > 0) EXEC dbo.TruncateTableRec ‘ + @FKTableName + ‘,@LEVEL1’
  EXEC (@SQL)    
  
  PRINT @TABLE + ‘5’
  SET @SQL = ‘ALTER TABLE [dbo].[‘+@FKTableName+’] ADD CONSTRAINT [‘+@ForeignKey+’] FOREIGN KEY([‘+@FKColumnName+’]) REFERENCES [dbo].[‘+@PKTableName+’] ([‘+@PKColumnName+’])’
  
  EXEC (@SQL)  
  
  UPDATE #FKKeysTable  SET CREATECon = 1 WHERE ForeignKey = @ForeignKey
  
  SET @ForeignKey = ”
  
  SELECT TOP 1  @PKTableName = PKTableName, @PKColumnName = PKColumnName,
   @FKTableName = FKTableName, @FKColumnName = FKColumnName, @ForeignKey = ForeignKey
  FROM #FKKeysTable
  where CREATECon = 0  AND NESTLEVEL = @LEVEL
 END
END
GO

———————————

CREATE PROCEDURE [dbo].[ShowTruncateTableRec]
 @TABLE   VARCHAR(100),
 @LEVEL   INT
AS
BEGIN

 DECLARE @PKTableName varchar(255),
   @PKColumnName varchar(255),
   @FKTableName varchar(255),
   @FKColumnName varchar(255),
   @ForeignKey varchar(255),
   @SQL varchar(3000)
 
 INSERT INTO #FKKeysTable
  SELECT
   OBJECT_NAME (f.referenced_object_id) AS PKTableName,
   COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS PKColumnName, 
   OBJECT_NAME(f.parent_object_id) AS FKTableName,
   COL_NAME(fc.parent_object_id, fc.parent_column_id) AS FKColumnName,
   f.name AS ForeignKey,
   0,
   0,
   @LEVEL
  FROM
   sys.foreign_keys AS f
   INNER JOIN sys.foreign_key_columns AS fc ON fc.constraint_object_id = f.OBJECT_ID   
   LEFT OUTER JOIN #FKKeysTable AS fkt ON fkt.PKTableName = @TABLE
  where
   OBJECT_NAME (f.referenced_object_id) = ” + @TABLE + ”     
   AND (fkt.PKTableName IS NULL AND fkt.PKColumnName IS NULL AND fkt.FKTableName IS NULL
   AND fkt.FKColumnName IS NULL AND fkt.ForeignKey IS NULL)
  order by
   PKTableName 
 
 SELECT TOP 1  @PKTableName = PKTableName, @PKColumnName = PKColumnName,
  @FKTableName = FKTableName, @FKColumnName = FKColumnName, @ForeignKey = ForeignKey
 FROM #FKKeysTable
 where CREATECon = 0 AND NESTLEVEL = @LEVEL
 
 WHILE (ISNULL(@ForeignKey, ”) <> ” )
 BEGIN
  –if(@LEVEL + 1 < 31)
  –BEGIN
   SET @SQL = ‘DECLARE @LEVEL1 INT SET @LEVEL1 =’ + CAST(@LEVEL AS VARCHAR(10))+ ‘ SET @LEVEL1 = @LEVEL1 + 1 IF((SELECT COUNT(*) FROM ‘ + @FKTableName + ‘) > 0) EXEC dbo.ShowTruncateTableRec ‘ + @FKTableName + ‘,@LEVEL1’
   EXEC (@SQL)      
  –END
  
  UPDATE #FKKeysTable  SET CREATECon = 1 WHERE ForeignKey = @ForeignKey
  
  SET @ForeignKey = ”
  
  SELECT TOP 1  @PKTableName = PKTableName, @PKColumnName = PKColumnName,
   @FKTableName = FKTableName, @FKColumnName = FKColumnName, @ForeignKey = ForeignKey
  FROM #FKKeysTable
  where CREATECon = 0  AND NESTLEVEL = @LEVEL
 END
 
END
GO

——————————-

— Testing
/*
CREATE TABLE [dbo].[PKTable](
 [Col1] [int] IDENTITY(1,1) NOT NULL,
 [Col2] [varchar](50) NULL,
 CONSTRAINT [PK_PKTable] PRIMARY KEY CLUSTERED
(
 [Col1] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[FKTable](
 [FKCol] [int] IDENTITY(1,1) NOT NULL,
 [Col1] [int] NOT NULL,
 [Col3] [varchar](50) NULL,
 CONSTRAINT [PK_FKTable] PRIMARY KEY CLUSTERED
(
 [FKCol] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[FKTableLeve2](
 [FKCol] [int] IDENTITY(1,1) NOT NULL,
 [Col1] [int] NOT NULL,
 [Col2] [int] NOT NULL,
 [Col3] [varchar](50) NULL,
 CONSTRAINT [PK_FKTableLevel2] PRIMARY KEY CLUSTERED
(
 [FKCol] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

— Add FOREIGN KEY Constraints
ALTER TABLE [dbo].[FKTable] ADD CONSTRAINT [FK_Col1] FOREIGN KEY([Col1])
REFERENCES [dbo].[PKTable] ([Col1])

ALTER TABLE [dbo].[FKTableLeve2] ADD CONSTRAINT [FKLevel2_Col1] FOREIGN KEY([Col1])
REFERENCES [dbo].[FKTable] ([FKCol])

ALTER TABLE [dbo].[FKTableLeve2] ADD CONSTRAINT [FKLevel3_Col1] FOREIGN KEY([Col2])
REFERENCES [dbo].[PKTable] ([Col1])

— INSERT Data
INSERT INTO PKTable VALUES(‘a’)
INSERT INTO PKTable VALUES(‘b’)

INSERT INTO FKTable VALUES(1,’a’)
INSERT INTO FKTable VALUES(2,’b’)

INSERT INTO FKTableLeve2 VALUES(1, 1, ‘a’)
INSERT INTO FKTableLeve2 VALUES(2, 1, ‘b’)

— Execute Procedure to see all the tables (including the recursive sub tables) that PKTable one is related with
EXEC TruncateTable ‘PKTable’
— Execute Procedure with flag 0 to truncate the data with out effecting the Foreign Key relations
EXEC TruncateTable ‘PKTable’, 0
*/