Sunday, May 12, 2013

Sql Server: Tables which are not dependent , or which do not reference other table’s

Find the tables which are not dependent on any other table, or which do not reference other table’s column for more information.

---create a table which will store all the tables name and id in a database, which do not depend on other table , or which doesn'd refernece to other tables

CREATE TABLE NotParent(Object_id1 INT,name NVARCHAR(50))

--use sys.foreign_keys table which contains all the relation between the tables through foreign_key constraints. Here parent_object_id are the tabels’object_id, which refernce to other table for more information.

--use sp_MSForeachtable, which is a Stored procedure performs operation on each table of the database. This will compare each table’s object_id, with the parent_object_id in sys.foreign_key table, and will fetch those object_id which are not parent , that means, which doesn't reference to any other table

EXEC sp_MSforeachtable
@command1='INSERT INTO NotParent
           SELECT OBJECT_ID(''?''),''?''
           WHERE ''?'' NOT IN (
                   SELECT DISTINCT ''?'' as table_name                                              
                   FROM sys.foreign_keys WHERE parent_object_id=OBJECT_ID(''?'')


The Result of NotParent table can be used for various purposes for e.g; deleteion of  the contents or these table first ,as these tables will not throw any error, because these doesn’t reference to other tables. 

Please provide your feedback for the post, if you find this post useful. Also Post your query or scenario, i will be happy to help.

No comments:

Post a Comment