SQL – Drop and Rebuild Foreign Keys, Dynamic ALTER Statements

In the data warehousing world of relationship, identification at the earliest is vital (lets say we are targeting a star schema Kimball model).

But at times you miss out on the critical information during requirement phase and later is brought (even change of business requirements) on table that you have add a new dimension to the model for further slice n dice capability !!! Pheww…

(Well, Data vault is great in this scenario, but lets stick back to Kimball world for this post)

So facing such challenges in my day to day work, I pick upon a MS SQL statement which is an absolute life saver for me and I hope it comes in handy for you as well, in such times of change

declare @tableName varchar(64);
set @tableName = 'FactInternetSales';

select
SO_P.name as [parent table]
,SC_P.name as [parent column]
,'is a foreign key of' as [direction]
,SO_R.name as [referenced table]
,SC_R.name as [referenced column]
,'ALTER TABLE ['+SCH_P.name+'].['+SO_P.name+'] DROP CONSTRAINT ['+foreignKey.name+']' AS '[DropCommand]'
,'ALTER TABLE [' + SCH_P.name + '].[' + SO_P.name + '] 
ADD CONSTRAINT [' + foreignKey.name +'] FOREIGN KEY (' + SC_P.name +') REFERENCES [' + SCH_R.name + '].[' + SO_R.name + '](' + SC_R.name + ') 
GO' AS [Create Command]

from sys.foreign_key_columns FKC
inner join sys.objects SO_P on SO_P.object_id = FKC.parent_object_id
inner join sys.columns SC_P on (SC_P.object_id = FKC.parent_object_id) AND (SC_P.column_id = FKC.parent_column_id)
inner join sys.objects SO_R on SO_R.object_id = FKC.referenced_object_id
inner join sys.columns SC_R on (SC_R.object_id = FKC.referenced_object_id) AND (SC_R.column_id = FKC.referenced_column_id)
INNER JOIN sys.schemas SCH_P ON SO_P.schema_id = SCH_P.SCHEMA_ID
INNER JOIN sys.schemas SCH_R ON SO_R.schema_id = SCH_R.schema_id
INNER JOIN sys.objects foreignKey ON foreignKey.object_id = FKC.constraint_object_id
where
    ((SO_P.name = @tableName) AND (SO_P.type = 'U'))
    OR
    ((SO_R.name = @tableName) AND (SO_R.type = 'U'))

In the above query, the variable “@tableName” is the table to which all the referring columns we need to identify, and generate dynamically the ALTER SQL statements to drop and recreate the relationships.

The resultant of the above is as follows:

And voila, now I can easily drop and recreate foreign keys referencing the table (Factual) and alter its design to heart content.

The above query provides me with the DROP and CREATE statements for the references pointing to the table columns.

Hope it helps…