Self Referenced Tables.

[Xandip]

Hi all. Please guide me thru this. I have a large database with many tables having self references. for eg: TestTable: TestId int, TestName varchar(100), ParentTestId int, Primary Key (TestId), Foreign Key (ParentTestId) References TestTable(TestId) I just wanted to query out the tables which are having similar type of relationships within themselves. Thanks in advance.

The name is Sandeep

[Parwej Ahamad]

[Message Deleted]

[Xandip]

thanks for the reply man. but that's not what i asked. :) I don't want to select any DATA from any table. I just wanted to know whether there are any other tables in my database, which have self references within themselves [just like the reference established in TestTable] I wanted a query which shows those type of tables.

The name is Sandeep

[Krishnraj]

Hi Xandip, Just try below query. and yes run it in SQL Server 2000

select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where Table_Name = 'TestTable'
and Constraint_Type = 'FOREIGN KEY'

Hope it will help u.

Krishnraj

[Xandip]

thanx buddy, but isnt this the query to list the foreign keys of a table? anyways, i think i got my answer select * from sys.objects where object_id in (select parent_object_id from sys.foreign_key_columns where parent_object_id = referenced_object_id) Thanks for your effort guys..:)

The name is Sandeep

[Krishnraj]

Yes, my posted query displays the name of the table and all its constraints. but in sql server 2000. I see yr query and its of sql server 2005. anyways, now u got the solution. but its just for yr info.. :)

Krishnraj