# Miscellaneous > SQL Scripts >  script FK constraints

## Ads

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

Thanks!

----------


## skhanal

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.  :Big Grin:

----------


## Ads

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!

----------


## skhanal

You get exactly that from EM or DMO.

----------


## Ads

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

----------


## YuckFou

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

----------


## Ads

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

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  :Frown: 

Thanks!

----------


## YuckFou

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)

----------


## Ads

Works great! Thanks :Smilie:

----------

