Script for all Foreign Keys of all tables of a DB

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Henk Schreij » Script for all Foreign Keys of all tables of...

Script for all Foreign Keys of all tables of a DB

Rate Topic

Display Mode

Topic Options

Author

Message

Henk Schreij

Posted Saturday, September 29, 2007 6:52 AM

SSC Veteran

Group: General Forum Members
Last Login: Monday, May 13, 2013 3:02 AM
Points: 235, Visits: 560

Comments posted to this topic are about the item Script for all Foreign Keys of all tables of a DB

Post #404378

neongladiator

Posted Thursday, May 14, 2009 4:46 PM

Forum Newbie

Group: General Forum Members
Last Login: Friday, March 18, 2011 12:42 PM
Points: 7, Visits: 85

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

Post #717479

MudLuck

Posted Wednesday, June 03, 2009 1:49 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 5:32 PM
Points: 125, Visits: 453

Hey Neon,

How do you account for FK that have more then one column? This query wont work for those. food for thought.

Post #728500

neongladiator

Posted Wednesday, June 03, 2009 6:31 PM

Forum Newbie

Group: General Forum Members
Last Login: Friday, March 18, 2011 12:42 PM
Points: 7, Visits: 85

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

Post #728612

« Prev Topic | Next Topic »

Permissions