Create PROCEDURE [dbo].[TruncateTableAndDropCreateRelations]
AS
BEGIN
    DECLARE @Fk_Name VARCHAR(MAX),
            @ForeignTable VARCHAR(MAX),
            @ForeignField VARCHAR(MAX),
            @PrimaryTable VARCHAR(MAX),
            @PrimaryField VARCHAR(MAX),
            @IsDeleteCascade BIT,
            @IsUpdateCascade BIT,
            @Script VARCHAR(MAX);

    DECLARE @CacheTable AS TABLE
    (
        Fk_Name VARCHAR(MAX),
        ForeignTable VARCHAR(MAX),
        ForeignField VARCHAR(MAX),
        PrimaryTable VARCHAR(MAX),
        PrimaryField VARCHAR(MAX),
        IsDeleteCascade BIT,
        IsUpdateCascade BIT
    );

    INSERT INTO @CacheTable
    (
        Fk_Name,
        ForeignTable,
        ForeignField,
        PrimaryTable,
        PrimaryField,
        IsDeleteCascade,
        IsUpdateCascade
    )
    SELECT fk.name,
           tp.name,
           cp.name,
           tr.name,
           cr.name,
           fk.delete_referential_action,
           fk.update_referential_action
    FROM sys.foreign_keys fk
        INNER JOIN sys.tables tp
            ON fk.parent_object_id = tp.object_id
        INNER JOIN sys.tables tr
            ON fk.referenced_object_id = tr.object_id
        INNER JOIN sys.foreign_key_columns fkc
            ON fkc.constraint_object_id = fk.object_id
        INNER JOIN sys.columns cp
            ON fkc.parent_column_id = cp.column_id
               AND fkc.parent_object_id = cp.object_id
        INNER JOIN sys.columns cr
            ON fkc.referenced_column_id = cr.column_id
               AND fkc.referenced_object_id = cr.object_id
    WHERE tr.name IN ( 'TmpHeadh', 'TmpHavaleh', 'Headh', 'Havaleh', 'TmpHeads', 'TmpSanad', 'Heads', 'Sanad',
                       'Sahmiyeh', 'Doctor', 'History_TmpDrugh', 'TmpDrugh', 'TmpDrugHavaleh', 'Drugh', 'DrugHavaleh'
                     )
          AND tp.name NOT IN ( 'tblPT', 'tblCover_Bime', 'tbl_HParvande', 'TTACPrescription' );


    IF
    (
        SELECT COUNT(0)FROM @CacheTable
    ) = 0
        RETURN;

    BEGIN TRY
        BEGIN TRAN;

        TRUNCATE TABLE dbo.History_Delete_Sanad;
        TRUNCATE TABLE dbo.History_Detail_Factors;
        TRUNCATE TABLE dbo.History_Delete_HeadGh;
        TRUNCATE TABLE dbo.History_Delete_Ghest;
        IF EXISTS
        (
            SELECT *
            FROM dbo.sysobjects
            WHERE name = 'History_Delete_DrugH'
        )
            TRUNCATE TABLE dbo.History_Delete_DrugH;
        IF EXISTS
        (
            SELECT *
            FROM dbo.sysobjects
            WHERE name = 'History_Delete_DrugHavaleh'
        )
            TRUNCATE TABLE dbo.History_Delete_DrugHavaleh;

        DECLARE CursorLink CURSOR FOR
        SELECT Fk_Name,
               ForeignTable
        FROM @CacheTable;
        OPEN CursorLink;
        FETCH NEXT FROM CursorLink
        INTO @Fk_Name,
             @ForeignTable;
        WHILE @@FETCH_STATUS = 0
        BEGIN
            EXEC ('ALTER TABLE [dbo].[' + @ForeignTable + '] DROP CONSTRAINT ' + @Fk_Name);
            FETCH NEXT FROM CursorLink
            INTO @Fk_Name,
                 @ForeignTable;
        END;
        CLOSE CursorLink;
        DEALLOCATE CursorLink;

        DECLARE CursorLink CURSOR FOR
        SELECT ForeignTable 'Name'
        FROM @CacheTable
        UNION
        SELECT PrimaryTable 'Name'
        FROM @CacheTable
        WHERE PrimaryTable NOT IN ( 'Sahmiyeh', 'Doctor' );
        OPEN CursorLink;
        FETCH NEXT FROM CursorLink
        INTO @ForeignTable;
        WHILE @@FETCH_STATUS = 0
        BEGIN
            EXEC ('TRUNCATE TABLE ' + @ForeignTable);
            FETCH NEXT FROM CursorLink
            INTO @ForeignTable;
        END;
        CLOSE CursorLink;
        DEALLOCATE CursorLink;

        DECLARE CursorLink CURSOR FOR
        SELECT Fk_Name,
               ForeignTable,
               ForeignField,
               PrimaryTable,
               PrimaryField,
               IsDeleteCascade,
               IsUpdateCascade
        FROM @CacheTable;
        OPEN CursorLink;
        FETCH NEXT FROM CursorLink
        INTO @Fk_Name,
             @ForeignTable,
             @ForeignField,
             @PrimaryTable,
             @PrimaryField,
             @IsDeleteCascade,
             @IsUpdateCascade;
        WHILE @@FETCH_STATUS = 0
        BEGIN
            SET @Script
                = ' ALTER TABLE [dbo].[' + @ForeignTable + '] ADD CONSTRAINT [' + @Fk_Name + '] FOREIGN KEY (['
                  + @ForeignField + ']) REFERENCES [dbo].[' + @PrimaryTable + ']([' + @PrimaryField + '])';
            IF @IsDeleteCascade = 1
                SET @Script = @Script + ' ON DELETE CASCADE';
            IF @IsUpdateCascade = 1
                SET @Script = @Script + ' ON UPDATE CASCADE';
            EXEC (@Script);
            FETCH NEXT FROM CursorLink
            INTO @Fk_Name,
                 @ForeignTable,
                 @ForeignField,
                 @PrimaryTable,
                 @PrimaryField,
                 @IsDeleteCascade,
                 @IsUpdateCascade;
        END;
        CLOSE CursorLink;
        DEALLOCATE CursorLink;

        COMMIT TRAN;
    END TRY
    BEGIN CATCH
        IF CURSOR_STATUS('global', 'CursorLink') = 1
        BEGIN
            CLOSE CursorLink;
            DEALLOCATE CursorLink;
        END;
        DECLARE @ErrorMessage NVARCHAR(MAX),
                @ErrorSeverity INT,
                @ErrorState INT;
        SELECT @ErrorMessage = ERROR_MESSAGE() + N' Line ' + CAST(ERROR_LINE() AS NVARCHAR(5)),
               @ErrorSeverity = ERROR_SEVERITY(),
               @ErrorState = ERROR_STATE();
        ROLLBACK TRANSACTION;
        RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
    END CATCH;

END;
