Search

Showing posts with label List tables that doesn't participate in any relationships. Show all posts
Showing posts with label List tables that doesn't participate in any relationships. Show all posts

Monday, October 17, 2011

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