September 29, 2007 at 6:52 am
Comments posted to this topic are about the item Script for all Foreign Keys of all tables of a DB
May 14, 2009 at 4:46 pm
I recently needed to script out all foreign keys in a database, and figured I would post that script.
SELECT 'ALTER TABLE [' + RTRIM(tabowner.name) + '].[' + RTRIM(tab.name) + '] ADD CONSTRAINT ['
+ RTRIM(fkey.name) + '] FOREIGN KEY ([' + RTRIM(col.name) + ']) REFERENCES ['
+ RTRIM(rtabowner.name) + '].[' + RTRIM(rtab.name) + '] ([' + RTRIM(rcol.name) + '])'
FROM sysforeignkeys sfk (NOLOCK)
JOIN sysobjects tab (NOLOCK)
ON tab.id = sfk.fkeyid
JOIN sysobjects rtab (NOLOCK)
ON rtab.id = sfk.rkeyid
JOIN syscolumns col (NOLOCK)
ON col.id = tab.id
AND col.colid = sfk.fkey
JOIN syscolumns rcol (NOLOCK)
ON rcol.id = rtab.id
AND rcol.colid = sfk.rkey
JOIN sysobjects fkey (NOLOCK)
ON fkey.id = sfk.constid
JOIN sysusers tabowner (NOLOCK)
ON tabowner.uid = tab.uid
JOIN sysusers rtabowner (NOLOCK)
ON rtabowner.uid = rtab.uid
June 3, 2009 at 1:49 pm
Hey Neon,
How do you account for FK that have more then one column? This query wont work for those. food for thought.
June 3, 2009 at 6:31 pm
Hi Mud. I forgot to post the new version that I made once I remembered that not all FKs have only one column. Below is a longer version of the script that will account for any number of columns in the foreign keys.
--Declare variables used to concatenate column lists
DECLARE @column INT
, @maxcolumns INT
--Drop temp tables if they already exist
IF OBJECT_ID('tempdb..#foreignkeycolumns') IS NOT NULL DROP TABLE #foreignkeycolumns
IF OBJECT_ID('tempdb..#foreignkeys') IS NOT NULL DROP TABLE #foreignkeys
--Gather data for foreign key columns
SELECT FKeyID = fkey.id
, FKeyName = fkey.name
, TabOwner = tabowner.name
, TabID = tab.id
, TabName = tab.name
, ColName = col.name
, RTabOwner = rtabowner.name
, RTabID = rtab.id
, RTabName = rtab.name
, RColName = rcol.name
, KeyNo = sfk.keyno
INTO #foreignkeycolumns
FROM sysforeignkeys sfk (NOLOCK)
JOIN sysobjects tab (NOLOCK)
ON tab.id = sfk.fkeyid
JOIN sysobjects rtab (NOLOCK)
ON rtab.id = sfk.rkeyid
JOIN syscolumns col (NOLOCK)
ON col.id = tab.id
AND col.colid = sfk.fkey
JOIN syscolumns rcol (NOLOCK)
ON rcol.id = rtab.id
AND rcol.colid = sfk.rkey
JOIN sysobjects fkey (NOLOCK)
ON fkey.id = sfk.constid
JOIN sysusers tabowner (NOLOCK)
ON tabowner.uid = tab.uid
JOIN sysusers rtabowner (NOLOCK)
ON rtabowner.uid = rtab.uid
--Find distinct foreign keys
SELECT DISTINCT
FKeyID = FKeyID
, FKeyName = FKeyName
, TabOwner = TabOwner
, TabID = TabID
, TabName = TabName
, RTabOwner = RTabOwner
, RTabID = RTabID
, RTabName = RTabName
, Columns = CONVERT(VARCHAR(500),NULL)
, RColumns = CONVERT(VARCHAR(500),NULL)
INTO #foreignkeys
FROM #foreignkeycolumns
--Find max number of columns in any foreign key
SELECT @maxcolumns = MAX(KeyNo)
FROM #foreignkeycolumns
SET @column = 1
--Find first column in foreign key
UPDATE #foreignkeys
SET Columns = '[' + RTRIM(fkc.ColName) + ']'
, RColumns = '[' + RTRIM(fkc.RColName) + ']'
FROM #foreignkeys fk
JOIN #foreignkeycolumns fkc
ON fk.FKeyID = fkc.FKeyID
WHERE fkc.KeyNo = @column
--Concatenate list of columns for foreign keys
WHILE @column < @maxcolumns
BEGIN
SET @column = @column + 1
UPDATE #foreignkeys
SET Columns = Columns + ',[' + RTRIM(fkc.ColName) + ']'
, RColumns = RColumns + ',[' + RTRIM(fkc.RColName) + ']'
FROM #foreignkeys fk
JOIN #foreignkeycolumns fkc
ON fk.FKeyID = fkc.FKeyID
WHERE fkc.KeyNo = @column
END
--Create scripts for foreign keys
SELECT DISTINCT
'ALTER TABLE [' + RTRIM(TabOwner) + '].[' + RTRIM(TabName) + '] WITH NOCHECK ADD CONSTRAINT ['
+ RTRIM(FKeyName) + '] FOREIGN KEY (' + Columns + ') REFERENCES ['
+ RTRIM(RTabOwner) + '].[' + RTRIM(RTabName) + '] (' + RColumns + ')'
+ CHAR(10) + 'GO'
FROM #foreignkeys
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply