InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 14:25:54 GMT20http://www.sqlservercentral.com/Forums/Topic404378-708-1.aspxHi 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 listsDECLARE @column INT, @maxcolumns INT--Drop temp tables if they already existIF OBJECT_ID('tempdb..#foreignkeycolumns') IS NOT NULL DROP TABLE #foreignkeycolumnsIF OBJECT_ID('tempdb..#foreignkeys') IS NOT NULL DROP TABLE #foreignkeys--Gather data for foreign key columnsSELECT 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.keynoINTO #foreignkeycolumnsFROM 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 keysSELECT 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 #foreignkeysFROM #foreignkeycolumns--Find max number of columns in any foreign keySELECT @maxcolumns = MAX(KeyNo)FROM #foreignkeycolumnsSET @column = 1--Find first column in foreign keyUPDATE #foreignkeysSET Columns = '[' + RTRIM(fkc.ColName) + ']', RColumns = '[' + RTRIM(fkc.RColName) + ']'FROM #foreignkeys fk JOIN #foreignkeycolumns fkc ON fk.FKeyID = fkc.FKeyIDWHERE fkc.KeyNo = @column--Concatenate list of columns for foreign keysWHILE @column < @maxcolumnsBEGIN 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 = @columnEND--Create scripts for foreign keysSELECT 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 #foreignkeysWed, 03 Jun 2009 18:31:20 GMTneongladiatorhttp://www.sqlservercentral.com/Forums/Topic404378-708-1.aspxHey Neon,How do you account for FK that have more then one column? This query wont work for those. food for thought.Wed, 03 Jun 2009 13:49:48 GMTMudLuckhttp://www.sqlservercentral.com/Forums/Topic404378-708-1.aspxI 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.uidThu, 14 May 2009 16:46:07 GMTneongladiatorhttp://www.sqlservercentral.com/Forums/Topic404378-708-1.aspxComments posted to this topic are about the item [B]<A HREF="/scripts/Miscellaneous/31856/">Script for all Foreign Keys of all tables of a DB</A>[/B]Sat, 29 Sep 2007 06:52:40 GMTHenk Schreij