Foreign key question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Foreign key question

Author

Topic

tocroi72
Yak Posting Veteran

89 Posts

Posted - 2005-12-15 : 09:38:25

Hi all,
If i have a contraint named "Ref1111"
can anyone show me a query to find out the 2 table it is referenced to?

Thanks for your help

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2005-12-15 : 09:51:36

I don't know about an easy (or standard) way, but here is my solution:
Script all tables with constraints.
Paste it in Query Analyzer (or any word processor)
Search for "Ref1111"
Here u go

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-15 : 09:58:02

Look for the tables INFORMATION_SCHEMA.TABLE_CONSTRAINTS and INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE

Madhivanan

Failing to plan is Planning to fail

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-12-15 : 09:59:14

You can do this:

select TABLE_NAME from information_schema.table_constraints
where CONSTRAINT_NAME = 'Ref1111'

exec sp_fkeys >result from above here<

Hi Maddy

!

rockmooose

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-15 : 10:02:51

>>Hi Maddy !

No problem

Madhivanan

Failing to plan is Planning to fail

tocroi72
Yak Posting Veteran

89 Posts

Posted - 2005-12-15 : 10:13:19

Thank you all for your quick helps. All solutions you gave me only return one foreign key table - i was looking for both tables (primary key table and foreign key table) without having to script out the tables themselves.

Thanks again.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-12-15 : 10:34:40

Can we assume your constraint is a foreign key constraint? If so, will this do it for you?:


use northwind
go
create table junk1 (junk1id int identity(1,1) primary key, ji int)
go
create table junk2 (junk2id int identity(1,1) primary key
, junk1id int constraint fk_junk2_junk1id  foreign key references junk1(junk1id))
go
create table junk3 (junk3id int identity(1,1) primary key
, junk1id int constraint fk_junk3_junk1id  foreign key references junk1(junk1id))
go
select	rc.constraint_name
	,uc.table_name UC_Table
	,fk.table_name FK_Table
from	information_schema.referential_constraints rc
join	information_schema.constraint_column_usage fk
		on fk.constraint_name = rc.constraint_name
join	information_schema.constraint_column_usage uc
		on uc.constraint_name = rc.unique_constraint_name
where rc.constraint_name  in ('fk_junk2_junk1id', 'fk_junk3_junk1id')

go
drop table junk3
drop table junk2
drop table junk1
go

Be One with the Optimizer
TG

tocroi72
Yak Posting Veteran

89 Posts

Posted - 2005-12-15 : 10:39:54

that's it.

Thank you so much TG.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-12-15 : 10:44:08

sp_fkeys does work, btw.
good script TG.

tocroi72
Yak Posting Veteran

89 Posts

Posted - 2005-12-15 : 10:55:24

rockmoose,
sp_fkeys does work - sorry i over looked.

Thanks

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-12-15 : 11:00:58

thanks, rockmoose.

Does it work?
I looked at sp_fkeys first but it only seems to take a table name rather than a constraint name as input. and sp_helpconstraint doesn't show referencing tables.

Be One with the Optimizer
TG

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-12-15 : 11:33:25

sp_fkey only takes a table name, yes.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources