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.
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

No comments:

Post a Comment