Regular reader of SQLAuthority.com blog Madhaiyan Seenivasan has send email with one very interesting script. This script generates all the foreign key addition script for your database. Many times there are situations where one need to drop all the foreign key and add them back. This SQL Script can be used for the same purpose. Run it to generate foreign key scripts before you drop the keys.
You can execute the SP by executing its name like
EXEC DBO.SPGetForeignKeyInfo IF EXISTS ( SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[SPGetForeignKeyInfo]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE dbo.SPGetForeignKeyInfo GO CREATE PROCEDURE DBO.SPGetForeignKeyInfo AS /* Author : Seenivasan This procedure is used for Generating Foreign Key script. */ SET NOCOUNT ON DECLARE @FKName NVARCHAR(128) DECLARE @FKColumnName NVARCHAR(128) DECLARE @PKColumnName NVARCHAR(128) DECLARE @fTableName NVARCHAR(128) DECLARE @fUpdateRule INT DECLARE @fDeleteRule INT DECLARE @FieldNames NVARCHAR(500) CREATE TABLE #Temp( PKTABLE_QUALIFIER NVARCHAR(128), PKTABLE_OWNER NVARCHAR(128), PKTABLE_NAME NVARCHAR(128), PKCOLUMN_NAME NVARCHAR(128), FKTABLE_QUALIFIER NVARCHAR(128), FKTABLE_OWNER NVARCHAR(128), FKTABLE_NAME NVARCHAR(128), FKCOLUMN_NAME NVARCHAR(128), KEY_SEQ INT, UPDATE_RULE INT, DELETE_RULE INT, FK_NAME NVARCHAR(128), PK_NAME NVARCHAR(128), DEFERRABILITY INT) DECLARE TTableNames CURSOR FOR SELECT name FROM sysobjects WHERE xtype = 'U' OPEN TTableNames FETCH NEXT FROM TTableNames INTO @fTableName WHILE @@FETCH_STATUS = 0 BEGIN INSERT #Temp EXEC dbo.sp_fkeys @fTableName FETCH NEXT FROM TTableNames INTO @fTableName END CLOSE TTableNames DEALLOCATE TTableNames SET @FieldNames = '' SET @fTableName = '' SELECT DISTINCT FK_NAME AS FKName,FKTABLE_NAME AS FTName, @FieldNames AS FTFields,PKTABLE_NAME AS STName, @FieldNames AS STFields,@FieldNames AS FKType INTO #Temp1 FROM #Temp ORDER BY FK_NAME,FKTABLE_NAME,PKTABLE_NAME DECLARE FK_CUSROR CURSOR FOR SELECT FKName FROM #Temp1 OPEN FK_CUSROR FETCH FROM FK_CUSROR INTO @FKName WHILE @@FETCH_STATUS = 0 BEGIN DECLARE FK_FIELDS_CUSROR CURSOR FOR SELECT FKCOLUMN_NAME,PKCOLUMN_NAME,UPDATE_RULE,DELETE_RULE FROM #TEMP WHERE FK_NAME = @FKName ORDER BY KEY_SEQ OPEN FK_FIELDS_CUSROR FETCH FROM FK_FIELDS_CUSROR INTO @FKColumnName,@PKColumnName, @fUpdateRule,@fDeleteRule WHILE @@FETCH_STATUS = 0 BEGIN UPDATE #Temp1 SET FTFields = CASE WHEN LEN(FTFields) = 0 THEN '['+@FKColumnName+']' ELSE FTFields +',['+@FKColumnName+']' END WHERE FKName = @FKName UPDATE #Temp1 SET STFields = CASE WHEN LEN(STFields) = 0 THEN '['+@PKColumnName+']' ELSE STFields +',['+@PKColumnName+']' END WHERE FKName = @FKName FETCH NEXT FROM FK_FIELDS_CUSROR INTO @FKColumnName,@PKColumnName, @fUpdateRule,@fDeleteRule END UPDATE #Temp1 SET FKType = CASE WHEN @fUpdateRule = 0 THEN FKType + ' ON UPDATE CASCADE' ELSE FKType END WHERE FKName = @FKName UPDATE #Temp1 SET FKType = CASE WHEN @fDeleteRule = 0 THEN FKType + ' ON DELETE CASCADE' ELSE FKType END WHERE FKName = @FKName CLOSE FK_FIELDS_CUSROR DEALLOCATE FK_FIELDS_CUSROR FETCH next FROM FK_CUSROR INTO @FKName END CLOSE FK_CUSROR DEALLOCATE FK_CUSROR SELECT 'ALTER TABLE [dbo].['+FTName+'] ADD CONSTRAINT ['+FKName+'] FOREIGN KEY ('+FTFields+') REFERENCES ['+STName+'] ('+STFields+') '+FKType FROM #Temp1 SET NOCOUNT OFF RETURN GO
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
14 Comments. Leave new
so how come he says cusror instead of cursor? What if I just want to show all the tables with FKs and the FK names and the status if they are enabled or disabled?
hello friendz !!
I would like to know about foreign key reference in same database, my problem is –
I have 2 tables
table 1 –
CREATE TABLE [dbo].[Increment](
[Org_Value_ID] [numeric](9, 0) NOT NULL,
[Ph_Code] [numeric](9, 0) NOT NULL,
[Sr_No] [int] NOT NULL,
[Increment_S] [numeric](15,2) NULL,
[Increment_V] [numeric](15,2) NULL,
CONSTRAINT [PK_Increment] PRIMARY KEY CLUSTERED
(
[Org_Value_ID] ASC,
[Ph_Code] ASC,
[Sr_No] ASC
)) ON [PRIMARY]
Table 2 –
CREATE TABLE [dbo].[Org_Value](
[Org_Value_ID] [numeric](9, 0) NOT NULL,
[Ph_Code] [numeric](9, 0) NOT NULL,
[Entitled] [numeric](9, 2) NULL,
CONSTRAINT [PK_Org_Value] PRIMARY KEY CLUSTERED
(
[Org_Value_ID] ASC,
[Ph_Code] ASC
)) ON [PRIMARY]
I want to add foreign key reference from Org_value to Increment on Org_value_id & Ph_Code
when i tried –
ALTER TABLE [dbo].[Org_Value] WITH CHECK ADD CONSTRAINT [FK_Org_Value_Increment] FOREIGN KEY([Org_value_ID], [Ph_Code]) REFERENCES [dbo].[Increment] ([Org_Value_ID],[Ph_Code])
I am getting error –
There are no primary or candidate keys in the referenced table ‘dbo.Increment’ that match the referencing column list in the foreign key ‘FK_Org_Value_Increment’.
Please provide some comments, what should i do ?
select ‘ALTER TABLE ‘+object_name(a.parent_object_id)+
case when a.is_not_trusted = 1 then ‘ WITH NOCHECK ‘
else ” end +
‘ ADD CONSTRAINT ‘+ a.name +
‘ FOREIGN KEY (‘ + c.name + ‘) REFERENCES ‘ +
object_name(b.referenced_object_id) +
‘ (‘ + d.name + ‘)’
from sys.foreign_keys a
join sys.foreign_key_columns b
on a.object_id=b.constraint_object_id
join sys.columns c
on b.parent_column_id = c.column_id
and a.parent_object_id=c.object_id
join sys.columns d
on b.referenced_column_id = d.column_id
and a.referenced_object_id = d.object_id
where object_name(b.referenced_object_id) in
(select name from sys.tables)
order by c.name
Raknel – Thanks for the excellent suggestion/script
Hey man…I appreciate this posting.
It saved me a LOT of time and headache. A lot better than using the generate sql script wizard.
Thanks again.
I like this script and can see where it can save me a lot of time. However I can not get it to work on any schema other then dbo. What change can I make to use scheams?
HI Seenivasan,
Thanks a lot for the procedure. It saved lots of my time.
My version based off raknel’s above formats the sql like MSMSS does. Good day!
SELECT
‘ALTER TABLE dbo.[‘ + object_name(fKeys.parent_object_id) + ‘]’
+ CASE WHEN fKeys.is_not_trusted = 1
THEN ‘ WITH NOCHECK’
ELSE ”
END
+ ‘ ADD CONSTRAINT [‘ + fKeys.name + ‘]’
+ ‘ FOREIGN KEY’ + char(13)
+ ‘(‘
+ LEFT(
(
SELECT pCols.name + ‘, ‘ AS [text()]
FROM sys.foreign_key_columns fKeyCols
INNER JOIN sys.columns pCols ON fKeyCols.referenced_column_id = pCols.column_id AND fKeyCols.referenced_object_id = pCols.object_id
WHERE fKeyCols.constraint_object_id = fKeys.object_id
ORDER BY fKeyCols.referenced_column_id
FOR XML PATH(”)
),
LEN(
(
SELECT pCols.name + ‘, ‘ AS [text()]
FROM sys.foreign_key_columns fKeyCols
INNER JOIN sys.columns pCols ON fKeyCols.referenced_column_id = pCols.column_id AND fKeyCols.referenced_object_id = pCols.object_id
WHERE fKeyCols.constraint_object_id = fKeys.object_id
ORDER BY fKeyCols.referenced_column_id
FOR XML PATH(”)
)
) -1
)
+ ‘) REFERENCES ‘ + object_name(fKeys.referenced_object_id) + char(13)
+ ‘(‘
+ LEFT(
(
SELECT rCols.name + ‘, ‘ AS [text()]
FROM sys.foreign_key_columns fKeyCols
INNER JOIN sys.columns rCols ON fKeyCols.parent_column_id = rCols.column_id AND fKeyCols.parent_object_id = rCols.object_id
WHERE fKeyCols.constraint_object_id = fKeys.object_id
ORDER BY fKeyCols.referenced_column_id
FOR XML PATH(”)
),
LEN(
(
SELECT rCols.name + ‘, ‘ AS [text()]
FROM sys.foreign_key_columns fKeyCols
INNER JOIN sys.columns rCols ON fKeyCols.parent_column_id = rCols.column_id AND fKeyCols.parent_object_id = rCols.object_id
WHERE fKeyCols.constraint_object_id = fKeys.object_id
ORDER BY fKeyCols.referenced_column_id
FOR XML PATH(”)
)
) -1
)
+ ‘)’ + char(13)
+ ‘GO’
FROM
(
SELECT DISTINCT referenced_object_id, parent_object_id, object_id, name, is_not_trusted
FROM sys.foreign_keys fKeys
WHERE object_name(fKeys.referenced_object_id) = ‘BOOKINGLINEISSUE’
) AS fKeys
One more thing about my code… you have to output the results to text and then you have good formatting too.
Ow also thanks for the initial post. :)
Thanks for posting this. Previously, I used a simpler script, but it does not handle foreign keys with multiple columns. I was dreading the more complex coding. This is being done to programatically accomplish the FK drops and creates during sliding window partition maintenance.
Interestingly, none of these solutions seem to take schema usage into account.
Be sure to add a ‘0’ to your @@fetch_status = 0 and any other line that is missing anumber.
I know this is an old post, but for anyone else who regularly finds good SQL nuggets on this site and needs to be able to use this script with a different schema, all you need to do is:
1 – Modify the “EXEC dbo.sp_fkeys @fTableName” line to instead read “EXEC dbo.sp_fkeys @pktable_name = @fTableName,@pktable_owner = N’myschema’” (replace myschema with your schema name)
2 – Modify the “ALTER TABLE” lines to use your schema name in front of both the table name and the “REFERENCES” table name. (This assumes that all your pk and fk tables are in the same schema of course)
A bit of a manual effort, but it worked like a charm for me.
SQL Server 2014:
Msg 451, Level 16, State 1, Procedure SPGetForeignKeyInfo, Line 104
Cannot resolve collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “Latin1_General_CI_AS” in add operator occurring in SELECT statement column 1.