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
Foehammer@hotmail.com

"Foehammer" <foehammer@hotmail.com> wrote in message
news:27d68359.0407110735.9b75a48@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
> Foehammer@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