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.


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
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
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: ,


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.


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,

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 :)