zFE3eerC

1IF OBJECT_ID('[dbo].[SafeAddForeignKey]') IS NOT NULL
2BEGIN
3  PRINT N'Dropping [dbo].[SafeAddForeignKey]';
4  DROP PROCEDURE [dbo].[SafeAddForeignKey];
5END
6GO
7CREATE PROCEDURE [dbo].[SafeAddForeignKey]
8  @ThisTableName nvarchar(200),
9  @ThisColumnName nvarchar(200),
10  @ThatTableName nvarchar(200),
11  @ThatColumnName nvarchar(200),
12  @FkName nvarchar(300) = N'',
13  @CascadeDelete bit = 0
14AS
15BEGIN
16  IF NOT EXISTS 
17  (
18      SELECT 1 FROM [sys].[foreign_key_columns] [fk] 
19        INNER JOIN [sys].[columns] [pc] ON [pc].[object_id] = [fk].[parent_object_id] AND [pc].[column_id] = [fk].[parent_column_id] 
20        INNER JOIN [sys].[columns] [rc] ON [rc].[object_id] = [fk].[referenced_object_id] AND [rc].[column_id] = [fk].[referenced_column_id]
21      WHERE [fk].[parent_object_id] = object_id(@ThisTableName) AND [pc].[name] = @ThisColumnName
22      AND [fk].[referenced_object_id] = object_id(@ThatTableName) AND [rc].[NAME] = @ThatColumnName
23  )
24  BEGIN
25    DECLARE @sql nvarchar(max);
26    IF (LEN(@FkName) = 0)
27    BEGIN
28      SET @FkName = N'FK_' + @ThisTableName + N'_' + @ThatTableName;
29    END
30    
31    PRINT N'Adding [' + @FkName + N']';
32    SET @sql= N'ALTER TABLE [dbo].[' + @ThisTableName + N'] ' +
33      N'ADD CONSTRAINT [' + @FkName + N'] FOREIGN KEY ([' + @ThisColumnName + N']) ' +
34      N'REFERENCES [dbo].[' + @ThatTableName + N'] (' + @ThatColumnName + N')';
35      IF (@CascadeDelete = 1)
36      BEGIN
37        SET @sql = @sql + N' ON DELETE CASCADE';
38      END
39    EXEC (@sql);
40  END
41END
42GO