You are here:Home » tsql » AUTOMATED SP TRUNCATE TABLE WITH RELATION

AUTOMATED SP TRUNCATE TABLE WITH RELATION

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--EXEC SP_TRUNCATE 'TABLENAME'

CREATE PROCEDURE [dbo].[sp_truncate]
@pkey_table_name VARCHAR(256)
AS
BEGIN
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'#fkey_tables')
AND type in (N'S'))
DROP TABLE #fkey_tables;

DECLARE
@col_name VARCHAR(128),
@pkey_col_name VARCHAR(128),
@fkey VARCHAR(128),
@ref_table VARCHAR(128),
@fkey_col VARCHAR(128),
@sql NVARCHAR(512),
@sql_check NVARCHAR(128),
@num_of_fkeys TINYINT;

SELECT
--ss1.name+'.'+
so1.name [fkey_table]
,sc1.name [fkey_column]
,sfk.name [fkey_name]
--,ss.name+'.'+
,so.name [pkey_table]
,sc.name [pkey_column]
INTO #fkey_tables
FROM
sys.foreign_keys sfk
INNER JOIN sys.foreign_key_columns sfkc
ON sfk.object_id=sfkc.constraint_object_id
INNER JOIN sys.objects so
ON so.object_id=sfk.referenced_object_id
--INNER JOIN sys.schemas ss
--ON so.schema_id=ss.schema_id
INNER JOIN sys.columns sc
ON sc.object_id=so.object_id
AND sc.column_id=sfkc.referenced_column_id
INNER JOIN sys.objects so1
ON so1.object_id=sfkc.parent_object_id
INNER JOIN sys.columns sc1
ON sc1.object_id=so1.object_id
AND sc1.column_id=sfkc.parent_column_id
--INNER JOIN sys.schemas ss1
--ON so1.schema_id=ss1.schema_id
WHERE so.name=@pkey_table_name



SELECT [fkey_table],[fkey_name],COUNT(*) AS [num]
INTO #ref_data
FROM #fkey_tables
GROUP BY fkey_table,fkey_name;


DECLARE a_cur CURSOR STATIC FOR
SELECT
[fkey_table],
[fkey_name],
[num]
FROM #ref_data;

OPEN a_cur;
FETCH NEXT FROM a_cur INTO
@ref_table,
@fkey,
@num_of_fkeys;

WHILE (@@fetch_status=0)
BEGIN
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(@fkey)
AND parent_object_id = OBJECT_ID(@ref_table))
BEGIN
SET @sql = N'ALTER TABLE '+@ref_table+' DROP CONSTRAINT '+ @fkey;
PRINT @sql;
EXEC sp_executesql @sql;
END;

FETCH NEXT FROM a_cur INTO
@ref_table,
@fkey,
@num_of_fkeys;

END;
CLOSE a_cur;



SET @sql=N'truncate table '+ @pkey_table_name;
PRINT @sql;
EXEC sp_executesql @sql;

DECLARE @from_tbl varchar(256),
@fkeyn varchar(256),
@n tinyint;

DECLARE b_cur CURSOR STATIC FOR
SELECT
[pkey_table],
[pkey_column],
[fkey_name],
[fkey_table],
[fkey_column]
FROM #fkey_tables;

OPEN b_cur
FETCH NEXT FROM b_cur
INTO
@pkey_table_name,
@pkey_col_name,
@fkey,
@ref_table,
@fkey_col;



OPEN a_cur;

WHILE (@@fetch_status=0)
BEGIN


FETCH NEXT FROM a_cur INTO
@from_tbl,
@fkeyn,
@n;

WHILE (@@fetch_status=0)
BEGIN


IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(@fkeyn)
AND parent_object_id = OBJECT_ID(@from_tbl))
BEGIN

IF (@n>1)
BEGIN




SET @fkey_col=''
SET @pkey_col_name=''

SELECT @fkey_col=ISNULL(@fkey_col+',','')+[fkey_column],
@pkey_col_name=ISNULL(@pkey_col_name+',','')+[pkey_column]
FROM #fkey_tables
WHERE [fkey_name]=@fkeyn;

SET @fkey_col=substring(@fkey_col,2,len(@fkey_col))
SET @pkey_col_name=substring(@pkey_col_name,2,len(@fkey_col))

SET @sql = N'ALTER TABLE '+ @from_tbl+' WITH NOCHECK ADD CONSTRAINT '+ @fkeyn +' FOREIGN KEY('+@fkey_col+')
REFERENCES '+ @pkey_table_name+'('+@pkey_col_name+')';
SET @sql_check=N'ALTER TABLE '+ @from_tbl+' CHECK CONSTRAINT ALL';
END;
ELSE
BEGIN

SELECT @fkey_col=[fkey_column],
@pkey_col_name=[pkey_column]
FROM #fkey_tables
WHERE [fkey_name]=@fkeyn;
SET @sql = N'alter table '+ @from_tbl +' WITH NOCHECK ADD CONSTRAINT '+ @fkeyn +' FOREIGN KEY('+@fkey_col+')
REFERENCES '+ @pkey_table_name+'('+@pkey_col_name+')';
SET @sql_check=N'ALTER TABLE '+ @from_tbl+' CHECK CONSTRAINT ALL';
END;
print @sql;
EXEC sp_executesql @sql;
print @sql_check;
EXEC sp_executesql @sql_check;
END;
ELSE
BEGIN
print 'Foreign Key Already Exists'
print 'FKey name'+@fkeyn+' '+OBJECT_ID(@fkeyn)
print 'Referencing table'+@from_tbl+' '+OBJECT_ID(@from_tbl)
END;
FETCH NEXT FROM a_cur INTO
@from_tbl,
@fkeyn,
@n;
END;
FETCH NEXT FROM b_cur INTO
@pkey_table_name,
@pkey_col_name,
@fkey,
@ref_table,
@fkey_col;

END;
CLOSE b_cur;
CLOSE a_cur;
DEALLOCATE b_cur;
DEALLOCATE a_cur;

END;