Truncate Referenced Table

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:        Chris Morton    

-- Create date: 2009-01-05

-- Description:    Drops the Foriegn Keys, Truncates the table, Rebuilds the keys. please backup your DB first.



-- =============================================

CREATE PROCEDURE x_TruncateTable --'App_Users'

 @tablename SYSNAME

AS 

 BEGIN



 DECLARE @constraintname SYSNAME

 DECLARE @referencedcolumn SYSNAME

 DECLARE @foriegnkeyname SYSNAME

 DECLARE @foriegnkeytablename SYSNAME

 DECLARE @deleterule VARCHAR(15)

 DECLARE @updaterule VARCHAR(15)



 DECLARE @output TABLE

 (

 id TINYINT IDENTITY(1, 1),

 constrainttext NVARCHAR(512),

 droptext NVARCHAR(128)

 )



 DECLARE @fk TABLE

 (

 id TINYINT IDENTITY(1, 1),

 PKTABLE_QUALIFIER NVARCHAR(128),

 PKTABLE_OWNER NVARCHAR(128),

 PKTABLE_NAME NVARCHAR(128),

 PKCOLUMN_NAME NVARCHAR(128),

 FKTABLE_QUALIFIER NVARCHAR(128),

 FKTABLE_OWNER NVARCHAR(128),

 FKTABLE_NAME NVARCHAR(128),

 FKCOLUMN_NAME NVARCHAR(128),

 KEY_SEQ INT,

 UPDATE_RULE INT,

 DELETE_RULE INT,

 FK_NAME NVARCHAR(128),

 PK_NAME NVARCHAR(128),

 DEFERRABILITY INT

 )





 INSERT INTO @fk

 (

 PKTABLE_QUALIFIER,

 PKTABLE_OWNER,

 PKTABLE_NAME,

 PKCOLUMN_NAME,

 FKTABLE_QUALIFIER,

 FKTABLE_OWNER,

 FKTABLE_NAME,

 FKCOLUMN_NAME,

 KEY_SEQ,

 UPDATE_RULE,

 DELETE_RULE,

 FK_NAME,

 PK_NAME,

 DEFERRABILITY

 )

 EXEC ( 'sp_fkeys ''' + @tablename + ''''

 )







 DECLARE @counter INT

 SET @counter = 1

 WHILE @counter <= ( SELECT MAX(id)

 FROM @fk

 )

 BEGIN

 SELECT @referencedcolumn = FKCOLUMN_NAME,

 @constraintname = FK_NAME,

 @foriegnkeyname = PKCOLUMN_NAME,

 @foriegnkeytablename = FKTABLE_NAME,

 @deleterule = CASE DELETE_RULE

 WHEN 0 THEN ' ON DELETE CASCADE '

 ELSE ''

 END,

 @updaterule = CASE UPDATE_RULE

 WHEN 0 THEN ' ON UPDATE CASCADE'

 ELSE ''

 END

 FROM @fk

 WHERE id = @counter

 

 INSERT INTO @output

 SELECT 'ALTER TABLE [dbo].[' + @foriegnkeytablename

 + '] ADD

CONSTRAINT [' + @constraintname + '] FOREIGN KEY (' + @foriegnkeyname + ')

REFERENCES [' + @tablename + '] (' + @referencedcolumn + ') ' + @deleterule

 + @updaterule,

 'ALTER TABLE [dbo].[' + @foriegnkeytablename

 + '] DROP CONSTRAINT [' + @constraintname

 + ']'

 

 SET @counter = @counter + 1

 END 

    



 SELECT *

 FROM @output



 DECLARE @sqltext NVARCHAR(512)

    

 SET @counter = 1

 WHILE @counter <= ( SELECT MAX(id)

 FROM @OUTPUT

 )

 BEGIN

 

 

 SET @sqltext = ( SELECT droptext

 FROM @output

 WHERE id = @counter

 )

 

 EXEC ( @sqltext

 )

 

 SET @counter = @counter + 1

 END 



 EXEC ( 'TRUNCATE TABLE ' + @tablename

 )



 SET @counter = 1

 WHILE @counter <= ( SELECT MAX(id)

 FROM @OUTPUT

 )

 BEGIN

 SET @sqltext = ( SELECT constrainttext

 FROM @output

 WHERE id = @counter

 )

 

 EXEC ( @sqltext

 )

 

 

 SET @counter = @counter + 1

 END 





 END

GO