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(''?'')

                   )'

SELECT * FROM NotParent

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.

SSIS: Warning: The component "Lookup 1" (79) encountered duplicate reference key values

Warning: 

The component "Lookup 1" (79) encountered duplicate reference key values when caching reference data. This error occurs in Full Cache mode only. Either remove the duplicate key values, or change the cache mode to PARTIAL or NO_CACHE.

Solution and Explanation: 

It is OK to ignore this warning, 

The default cache mode for the lookup is Full cache. In this mode, the database is queried once during the pre-execute phase of the data flow. The entire reference set is pulled into memory.Full cached mode is usually the fastest.

In Partial cache mode, the lookup cache starts off empty at the beginning of the data flow. When a new row comes in, the lookup transform checks its cache for the matching values. If no match is found, it queries the database. If the match is found at the database, the values are cached so they can be used the next time a matching row comes in.

In No Cache mode, As the name implies, in this mode the lookup transform doesn't maintain a lookup cache (actually, not quite true - we keep the last match around, as the memory has already been allocated). In most situations, this means that you'll be hitting the database for every row.

Therefore its ok to ignore the warning, as full cache mode is the fastest. and can be used when you're accessing a large portion of your reference set or you have a small reference table.


Please provide your feedback for the post, if you find this post useful.