Determining if foreign key constraint exists

[Foehammer]

Hello,
I'm working with a number of databases. I need a way to determine if a
foreign key with given name exists and determine what tables and
fields are linked to it. Any ideas?

I know I can issue the following statement to get a list of the fields
that are constrained by a foreign key constraint.

SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE

How can I get a list of fields pointed to by the constraints?

Thanks,
Will
Fo*******@hotmail.com

[John Gilson]

"Foehammer" <fo*******@hotmail.com> wrote in message
news:27*************************@posting.google.co m...

Hello,
I'm working with a number of databases. I need a way to determine if a
foreign key with given name exists and determine what tables and
fields are linked to it. Any ideas?

I know I can issue the following statement to get a list of the fields
that are constrained by a foreign key constraint.

SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE

How can I get a list of fields pointed to by the constraints?

Thanks,
Will
Fo*******@hotmail.com

CREATE VIEW ForeignKeyReferences
(fk_name, fk_table_name, fk_column_name,
key_name, key_table_name, key_column_name, ordinal_position)
AS
SELECT FK.constraint_name, FK.table_name, FKU.column_name,
UK.constraint_name, UK.table_name, UKU.column_name,
FKU.ordinal_position
FROM Information_Schema.Table_Constraints AS FK
INNER JOIN
Information_Schema.Key_Column_Usage AS FKU
ON FK.constraint_type = 'FOREIGN KEY' AND
FKU.constraint_name = FK.constraint_name
INNER JOIN
Information_Schema.Referential_Constraints AS RC
ON RC.constraint_name = FK.constraint_name
INNER JOIN
Information_Schema.Table_Constraints AS UK
ON UK.constraint_name = RC.unique_constraint_name
INNER JOIN
Information_Schema.Key_Column_Usage AS UKU
ON UKU.constraint_name = UK.constraint_name AND
UKU.ordinal_position =FKU.ordinal_position

SELECT *
FROM ForeignKeyReferences
ORDER BY fk_table_name, fk_name, ordinal_position

--
JAG