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.
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
Reference : Pinal Dave (https://blog.sqlauthority.com) , Madhaiyan Seenivasan
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.