CREATE PROCEDURE [dbo].[TruncateTableAndDropCreateRelations] AS 
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')


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

BEGIN TRY
BEGIN TRAN

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() + ' Line ' + CAST(ERROR_LINE() AS NVARCHAR(5)), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();
    ROLLBACK TRANSACTION;
    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH



