Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Script for all Foreign Keys of all tables of a DB Expand / Collapse
Author
Message
Posted Saturday, September 29, 2007 6:52 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 2:14 AM
Points: 251, Visits: 656
Comments posted to this topic are about the item Script for all Foreign Keys of all tables of a DB


Post #404378
Posted Thursday, May 14, 2009 4:46 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Wednesday, June 3, 2009 1:49 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, July 25, 2014 11:04 PM
Points: 126, Visits: 500
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
Posted Wednesday, June 3, 2009 6:31 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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 »

Add to briefcase

Permissions Expand / Collapse