Thursday, January 04, 2007

List tables that doesn't participate in any relationships

This query returns those tables which satisfy the below two conditions:

1. Tables that do not contain any Foreign Key referencing other tables.
2. Tables that are not referenced by other tables using foreign key constraints.

Solution:

Till SQL Server 2000 days we used to write the below scripts [This still works with SQL Server 2005 also].

Select [name] as "Orphan Tables" from SysObjects where xtype='U' and id not in
(
Select fkeyID from SysForeignKeys
union
Select rkeyID from SysForeignKeys
)

Solution which works only with SQL Server 2005:

Method 1:

Select [name] as "Orphan Tables" from Sys.Tables where object_id not in
(
Select parent_object_id from Sys.Foreign_Keys
union
Select referenced_object_id from Sys.Foreign_Keys
)

Method 2:

Select ST.[Name] as "Orphan Tables"
from Sys.Foreign_Keys as SFK Right Join Sys.Tables as ST
On ST.object_id = SFK.parent_object_id Or
ST.object_id = SFK.referenced_object_id
Where SFK.type is null

Technorati tags: ,

4 comments:

Sanjeevi Kumar said...

Hi vadivel nice to see your blog with full of technical articles.

I also tried to list all the details of a table including relations, defaults, checks, rules, user-types, etc.

Check
http://ivision.wordpress.com/2006/12/30/getting-structure-of-a-table-or-all-tables-enhanced/

shankar said...
This comment has been removed by a blog administrator.
shankar said...

Hi Vadivel,

I am a big fan of yours blog. Your blog has made me to answer almost all queries which were raised in the interviews...:)

I have a doubt in database design and in structure of tables wat the google use for their Orkut community, that too specifically in replying zone in an orkut community. Do u have any idea about that.

With Regards,
T.Shankar.

Vadivel said...

Thanks Sanjeev and Sankar for dropping by my blogspace :)

Sanjeev : Would chk out ur article sometime this weekend.

Sankar : Yeah once we start analyzing the way orkut reply zone is working .. i guess we can simulate it. But i haven't taken pain to think in that angle yet as there wasn't any real need for me to do so :)