Self joined table list

Question

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

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

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 1

i found it difficult if i think on my top of mind but if those JOIN exist in stored proc then

select * from syscomments where text like '% INNER JOIN % ' but you need to play with this query further

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

ningaraju.n SSCrazy Points: 2322 More actions · Answer 2

Thanks ,but i did not get what i expected.

Let me quote the problem once again

I have User table where there is pk UserId and fk ParentId which refers pk UserId .

like this i have many tables in database

so i need an query to fetch this tables which are self joined

brdudley SSCrazy Points: 2320 More actions · Answer 3

Take a look at sys.foreign_keys. You will want to select records where the parent_object_id = referenced_object_id (means they are the same table)

This link has a query which will get you started.

I don't have a handy SQL instance, so I can't give you a tested result.

Good luck.

Brian

tfifield SSCrazy Eights Points: 9655 More actions · Answer 4

If you have them defined as Foreign Key constraints then this query should point you in the right direction:

SELECT object_id, OBJECT_NAME(object_id)

, parent_object_id, OBJECT_NAME(parent_object_id)

, referenced_object_id, OBJECT_NAME(referenced_object_id)

FROM sys.foreign_keys

WHERE parent_object_id = referenced_object_id

Todd Fifield

ningaraju.n SSCrazy Points: 2322 More actions · Answer 5

SSCrazy

Points: 2322

June 16, 2010 at 9:56 pm

Thanks Todd Fifield