Create Foreign Keys Dynamically in SQL Server Database

If you have created a database in which only the primary keys are defined, but at the after stage, you want to use the foreignkey also. In that case, it is very difficult to define the foreign keys in every table based on the primary key for the complete database. This thing can be done dynamically by creating a simple script that can read all the tables from the database and search for a field, and, based on that, create the foreign key if the searched field is available in the database table. The script for the same is attached.

Assumption: Assume that CompanyID is a Primary Key field that is used as a Foreign Key in other tables of the database.

Before running the script, the database has no Foreign Keys defined on any table.

This is the script for creating the Foreign Keys for all dependent tables:

Create a temp table to hold all user tables

IF OBJECT_ID('tempdb..#AllTables') IS NOT NULL DROP TABLE #AllTables;

-- Select all user-defined tables into a temporary table
SELECT name AS TableName
INTO #AllTables
FROM sys.tables
WHERE is_ms_shipped = 0;

-- Declare variables and cursor
DECLARE @TableName NVARCHAR(255);
DECLARE @SQL NVARCHAR(MAX);

DECLARE TableCursor CURSOR FOR
SELECT TableName FROM #AllTables;

-- Open cursor and iterate through each table
OPEN TableCursor;
FETCH NEXT FROM TableCursor INTO @TableName;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Check if 'CompanyID' column exists and no foreign key is defined (excluding 'CompanyMaster')
    IF EXISTS (
        SELECT 1
        FROM sys.columns
        WHERE object_id = OBJECT_ID(@TableName)
        AND name = 'CompanyID'
    ) AND NOT EXISTS (
        SELECT 1
        FROM sys.foreign_key_columns fkc
        JOIN sys.columns c 
            ON fkc.parent_column_id = c.column_id 
           AND fkc.parent_object_id = c.object_id
        WHERE c.name = 'CompanyID' 
        AND fkc.parent_object_id = OBJECT_ID(@TableName)
        AND @TableName <> 'CompanyMaster'
    )
    BEGIN
        -- Build and execute SQL to add a foreign key constraint
        SET @SQL = '
        ALTER TABLE [' + @TableName + ']
        ADD CONSTRAINT FK_' + @TableName + '_CompanyID
        FOREIGN KEY (CompanyID) REFERENCES Company(CompanyID);';

        EXEC sp_executesql @SQL;
    END

    FETCH NEXT FROM TableCursor INTO @TableName;
END

-- Clean up
CLOSE TableCursor;
DEALLOCATE TableCursor;
DROP TABLE #AllTables;

After running this script, the Foreign Keys are created. To check this.