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: SQL Server, SQL Server 2005
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: SQL Server, SQL Server 2005
Comments
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/
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.
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 :)