script FK constraints

Does anyone have a script that could script all the FK constraints on a database?

Thanks!

Are you using SQL 2000?.

If yes then you could generate the script from Enterprise Manager when you create scripts for tables. All FK script are created after create tables so you can easily get that from the script file generated.

If you want the fun way then you can use SQL DMO table object's script method. :D

Thanks for the quick response!
But I was really looking at scripting the FK like "Alter Table drop constraint...'
and "Alter Table Add constraint...'
through the system tables..any clue?

Thanks!

You get exactly that from EM or DMO.

sorry for the confusion...but this is what i am looking at...now this script is only able to give alters for FK with only 1 column, i want to get all FK columns included...

thanks a bunch!


CREATE PROCEDURE dbo.spGetFKConstraints
(
@DBNAME VARCHAR(128)
)
AS

DECLARE @QUERY VARCHAR(8000)

SET @QUERY =
'USE ' + @DBNAME + '

SELECT CAST(F.NAME AS VARCHAR(255)) AS ForeignKeyName,
CAST(c.name as varchar(255)) AS ForeignTable,
CAST(fc.name as varchar(255)) AS ForeignColumn,
cast(fc2.name as varchar(255)) as ForeignColumn2,
CAST(p.name as varchar(255)) AS PrimaryTable,
CAST(rc.name as varchar(255)) AS PrimaryColumn,
cast(rc2.name as varchar(255)) as PrimaryColumn2
INTO #GetFKConstraints
FROM SYSOBJECTS F
INNER JOIN SYSOBJECTS C ON F.PARENT_OBJ = C.ID
INNER JOIN SYSREFERENCES R on F.ID = R.CONSTID
INNER JOIN SYSOBJECTS P ON R.RKEYID = P.ID
INNER JOIN SYSCOLUMNS RC ON R.RKEYID = RC.ID AND R.RKEY1 = RC.COLID
INNER JOIN SYSCOLUMNS FC ON R.FKEYID = FC.ID AND R.FKEY1 = FC.COLID
left join syscolumns rc2 on r.rkeyid = rc2.id and r.rkey2 = rc.colid
left join syscolumns fc2 on r.fkeyid = fc2.id and r.fkey2 = fc.colid
WHERE F.TYPE = '+'''F'''+'

--SELECT * from #GetFKConstraints'+'

SELECT ' + '''ALTER TABLE ''' + '+ ForeignTable + ' + '''
DROP CONSTRAINT ''' + '+ ForeignKeyName
FROM #GetFKConstraints
ORDER BY ForeignTable'
+'

SELECT ' + '''ALTER TABLE ''' + '+ ForeignTable +' + '''
ADD CONSTRAINT ''' + '+ ForeignKeyName +'
+ ''' FOREIGN KEY ('''+ ' + ForeignColumn + '+ ''')
REFERENCES ''' + '+ PrimaryTable + ' + '''(''' + '+ PrimaryColumn + '+ ''')''' +
'FROM #GetFKConstraints
ORDER BY ForeignTable
'

EXEC (@QUERY)
GO

set nocount on
declare @MyTable table
( id int identity
, fkeyid int not null
, constid int not null
, keyno int null
, name varchar(40) not null)

---
insert into @MyTable (fkeyid , constid , keyno , name)
select fkeyid , constid , max(keyno) as keyno , OBJECT_NAME(constid) as name
from sysforeignkeys group by fkeyid , constid , rkeyid

--SCRIPT_1FK ADD CONSTRAINT...
select cmd as '-- script 1' from (

select id, 1 as num, 0 as keyno, 'ALTER TABLE '+OBJECT_NAME(fkeyid)+' ADD CONSTRAINT '+name+' FOREIGN KEY' as cmd
from @MyTable

union
--columns for fks - (foreign table)
select id, 2 as num, k.keyno
, case k.keyno when 1 then ' ' else ',' End
+COL_NAME(k.fkeyid,k.fkey)
+case k.keyno when i.keyno then ') REFERENCES '+OBJECT_NAME(k.rkeyid)+' (' else '' End as cmd
from @MyTable i , sysforeignkeys k
where k.fkeyid = i.fkeyid and k.constid = i.constid

union
--columns for fks - (reference table)
select id, 3 as num, k.keyno
, case k.keyno when 1 then ' ' else ',' End+COL_NAME(k.rkeyid,k.rkey) as cmd
from @MyTable i , sysforeignkeys k
where k.fkeyid = i.fkeyid and k.constid = i.constid

union

select id, 4 as num, 255 as keyno,');' as cmd
from @MyTable

) as viewx order by id,num,keyno

--SCRIPT_2FK DROP CONSTRAINT
select 'ALTER TABLE '+OBJECT_NAME(fkeyid)+' DROP CONSTRAINT '+OBJECT_NAME(constid) as '-- script 2'
from sysforeignkeys

Thanks YuckFou! This is what i was looking for!:)

The only problem is that while it works like a charm on some databases, on the others it is giving the following error:

String or binary data would be truncated.
The statement has been terminated.

It doesnt print the first script but prints the second script :(

Thanks!

I should used biger value for column [name] in @MyTable...
Try grove it up like this

declare @MyTable table
( id int identity
, fkeyid int not null
, constid int not null
, keyno int null
, name varchar(60) not null)


-- 60 or more the top value is 128 or datetype sysname>
...
, name sysname not null)

Works great! Thanks:)