sp_truncate

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

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;