Suppose you have just defined the primary keys in your database, but later on you want to use the foreign keys as well. In that scenario, defining the foreign keys in each table using the main key for the entire database is extremely challenging. This may be accomplished dynamically by writing a straightforward script that can read every table in the database, look for a field, and then, if the field is found in the database table, establish a foreign key. Attached is the script for the same.

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.

HostForLIFEASP.NET SQL Server 2022 Hosting