Search

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

No comments:

Post a Comment