I'm looking for a Script like that.....

[joecre]

SELECT
'if not exists (select* from dbo.sysobjects where (id = object_id(N'''+c_obj.name+'''))and OBJECTPROPERTY(id,N''IsConstraint'')=1) '
+' BEGIN '+
'ALTER TABLE ' + t_obj.name + ' ADD CONSTRAINT '+c_obj.name+
' FOREIGN KEY ' HERE ARE MISSING INFOS+
+' END '
AS Script
FROM sysobjects AS c_obj
LEFT OUTER JOIN syscomments AS com
ON c_obj.id = com.id
LEFT OUTER JOIN sysobjects AS t_obj
ON c_obj.parent_obj = t_obj.id
LEFT OUTER JOIN sysconstraints AS con
ON c_obj.id = con.constid
LEFT OUTER JOIN syscolumns AS col
ON t_obj.id = col.id and con.colid = col.colid
WHERE c_obj.xtype IN ('F') AND t_obj.name Like 'my_table'

to automatically add foreign keys in a table......
my question is : where can I find info on FK Constraints ?

I need to know which columns are used by the FK and if there are any clauses for CASCADE DELETE etc. etc.

Can someone suggest me a method ?
Thank you everybody

[vonarxma]

Wich database are you using?
There may be some database specific procedures...

[joecre]

I'm using SQL Server 2000 with Service Pack 3

[joecre]

SELECT
'if not exists (select* from dbo.sysobjects where (id = object_id(N'''+c_obj.name+'''))and OBJECTPROPERTY(id,N''IsForeignKey'')=1)' +CHAR(13)+
'BEGIN '+CHAR(13)+
'ALTER TABLE ' + t_obj.name + ' ADD CONSTRAINT ' + c_obj.name+
' FOREIGN KEY '+CHAR(13)+'('+CHAR(13)+col.name+CHAR(13)+
') REFERENCES '+ object_name(ref.rkeyid)+' ('+CHAR(13)+
col.name+CHAR(13)+ ') '+
CASE WHEN objectproperty(c_obj.id,'CnstIsDeleteCascade') = 1 THEN
+'ON DELETE CASCADE '
WHEN objectproperty(c_obj.id,'CnstIsUpdateCascade') = 1 THEN
+'ON DELETE CASCADE '
WHEN objectproperty(c_obj.id,'CnstIsNotRepl') = 1 THEN
+'NOT FOR REPLICATION '
ELSE
+''
END
+CHAR(13)+
'END '
AS Script
FROM sysobjects AS c_obj
LEFT OUTER JOIN syscomments AS com
ON c_obj.id = com.id
LEFT OUTER JOIN sysobjects AS t_obj
ON c_obj.parent_obj = t_obj.id
LEFT OUTER JOIN sysconstraints AS con
ON c_obj.id = con.constid
LEFT OUTER JOIN syscolumns AS col
ON t_obj.id = col.id and con.colid = col.colid
LEFT OUTER JOIN sysreferences AS ref
ON c_obj.id = ref.constid
WHERE c_obj.xtype IN ('F')

this is my script .
I've tried it from Query Analyzer and it seems to work.....
If someone will find something wrong in this script please post a message
Thank you everybody!!!!!