INFORMATION_SCHEMA question

Question

Post reply

INFORMATION_SCHEMA question

ArthurC

SSCommitted

Points: 1685
More actions
February 24, 2003 at 1:41 pm

#79771

Hi there,
I wish someone out there could give me a hand. I am having a problem for finding all tables in the database with reference to the specified table using INFORMATION_SCHEDMA view. Currently, I am using the following statement to achieve my task but I learned that this statement might not return a 100% accuracy.
SELECT DISTINCT o.name
FROM sysobjects o, sysforeignkeys f, sysobjects r
WHERE o.id = f.fkeyid
AND f.rkeyid = r.id
AND r.name = 'PSSL_T_PERSON'
Anyone can offer me a solution?
Millions thanks,
AC

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

gregj Old Hand Points: 346 More actions · Answer 1

Try this:

exec sp_MSforeachtable @command1 = "select top 1 * from ?"

Of course, you can replace the SQL command inside the quotes to be whatever you want, including calling another stored procedure. Just use ? where you want the name of the table.

ArthurC SSCommitted Points: 1685 More actions · Answer 2

Thanks Gregj,

I tried to run your recommended sp; however, it seems to me that this sp doesn't meet my requirement for returning all table names that reference to constraint for the specific table name. Does it make sense!!

AC

gregj Old Hand Points: 346 More actions · Answer 3

Yes, I understand now. I was too quick in trying to reply and didn't completely read your problem. Maybe I can reply later with something that actually helps!

BrendonSmith SSC-Addicted Points: 476 More actions · Answer 4

quote:
...using INFORMATION_SCHEDMA view.

I had to do this a while ago and remember it seemed a little ummm non-intuitive...well tricky anyway as it involves a loop join if thats what you call it?...

declare @SourceTable varchar(128)

set @sourcetable = 'customers'

SELECT

tc1.Table_name

FROMINFORMATION_SCHEMA.TABLE_CONSTRAINTS tc1

LEFT join INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc1 ON tc1.constraint_Name = rc1.constraint_Name

LEFT join INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc2 ON tc2.constraint_Name = rc1.unique_constraint_Name

WHERE

tc1.Constraint_Type = 'FOREIGN KEY' AND

LOWER(tc2.Table_name) = LOWER(@SourceTable) --Tables Referencing the Source Table

--compared to yours...

SELECT DISTINCT o.name

FROM sysobjects o, sysforeignkeys f, sysobjects r

WHERE o.id = f.fkeyid

AND f.rkeyid = r.id

AND r.name = 'customers'

Edited by - brendonsmith on 02/24/2003 7:10:58 PM

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 5

Try sp_fkeys.

Check http://qa.sqlservercentral.com/scripts/contributions/246.asp contributed by cneuhold

Edited by - Allen_Cui on 02/25/2003 07:51:18 AM

Edited by - Allen_Cui on 02/25/2003 07:52:31 AM

ArthurC SSCommitted Points: 1685 More actions · Answer 6

Thanks for you guys. The suggestion from BrendonSmith works for me. Again, I am very appreciated for you guys response.

AC