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
SELECTFKeyID = 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
SETColumns = '[' + 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
SETColumns = 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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy