Add Foreign Key Across Hundreds+ Data Warehouse Tables

Having spent time in different Data Warehouse environments inevitably there comes a point when you realize there is a DDL change that needs to happen that affects every table in the Warehouse.

I recently came up to the problem of needing to add a few different Foreign Key Constraints that would affect all the tables in the Data Warehouse. Adding them manually one by one was out of the question when your talking about hundreds or  thousands of tables.

Here is the simple script I came up with, using INFORMATION_SCHEMA information inside SQL Server to get the list of tables that contain the column that needs to be turned into a Foreign Key. Once you have those tables it’s easy enough to use Dynamic SQL to loop through those tables, creating the Foreign Key as you go along.

I my case it changed about 183 tables in 3 minutes, a little long, but way better
than writing out each one manually!

USE Your_Database;
DECLARE @SQL NVARCHAR(MAX), @TABLE NVARCHAR(100), @Schema NVARCHAR(100), @COLUMN NVARCHAR(100), @SourceTable NVARCHAR(100);
 
SET @COLUMN = 'Application_Id' --Set Column Name
SET @SourceTable = 'SourceApplication' -- Set Source Table that is the Primary
 
--Get all tables, besides source, that have Application_Id Column in them.
IF OBJECT_ID('tempdb..#Tables') IS NOT NULL DROP TABLE #Tables
SELECT COLUMN_NAME,TABLE_NAME,TABLE_SCHEMA
INTO #Tables
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = @COLUMN AND TABLE_NAME <> @SourceTable
 
--loop through tables adding the foreign key, note schema is hard coded for source table, you may have to change.
WHILE (SELECT COUNT(*) FROM #Tables) > 0
	BEGIN
		SET @TABLE = (SELECT TOP 1 TABLE_NAME FROM #Tables)
		SET @Schema = (SELECT TOP 1 TABLE_SCHEMA FROM #Tables)
		SET @SQL = 
			'ALTER TABLE [' + @Schema + '].[' + @TABLE + ']
				ADD CONSTRAINT FK_' + @Schema + '_'+ @TABLE +' FOREIGN KEY ([' + @COLUMN + '])
					REFERENCES dbo.['+ @SourceTable +'] ([' + @COLUMN + ']) 
						ON UPDATE CASCADE'
		EXEC sp_executesql @SQL
		DELETE FROM #Tables WHERE TABLE_NAME = @TABLE AND TABLE_SCHEMA = @Schema
	END