List tables on their dependency order based on foreign keys for Data Migration

If you want to perform bulk insert operation on multiple tables.

The tables have foreign key relationships between themselves.

If an INSERT operation is done on a table with a foreign key before the referenced table is being inserted to, the operation might fail due to violation of the foreign key.



Requirement



Produce a list of tables within a database ordered according to their dependencies.

Tables with no dependencies (no foreign keys) will be 1st.

Tables with dependencies only in the 1st set of tables will be 2nd.

Tables with dependencies only in the 1st or 2nd sets of tables will be 3rd.

and so on...





WITH cte (
 lvl
 ,object_id
 ,name
 ,schema_Name
 )
AS (
 SELECT 1
  ,object_id
  ,sys.tables.name
  ,sys.schemas.name AS schema_Name
 FROM sys.tables
 INNER JOIN sys.schemas ON sys.tables.schema_id = sys.schemas.schema_id
 WHERE type_desc = 'USER_TABLE'
  AND is_ms_shipped = 0
 
 UNION ALL
 
 SELECT cte.lvl + 1
  ,t.object_id
  ,t.name
  ,S.name AS schema_Name
 FROM cte
 JOIN sys.tables AS t ON EXISTS (
   SELECT NULL
   FROM sys.foreign_keys AS fk
   WHERE fk.parent_object_id = t.object_id
    AND fk.referenced_object_id = cte.object_id
   )
 JOIN sys.schemas AS S ON t.schema_id = S.schema_id
  AND t.object_id <> cte.object_id
  AND cte.lvl < 30
 WHERE t.type_desc = 'USER_TABLE'
  AND t.is_ms_shipped = 0
 )
SELECT schema_Name
 ,name
 ,MAX(lvl) AS dependency_level
FROM cte
WHERE schema_Name LIKE '%ORCA%'
GROUP BY schema_Name
 ,name
ORDER BY dependency_level
 ,schema_Name
 ,name;



Reference

Delete all Tables based on creation date

--Delete all Tables based on creation date



 DECLARE @tname VARCHAR(100)
DECLARE @sql VARCHAR(max)

DECLARE db_cursor CURSOR FOR
SELECT name AS tname
FROM sys.objects
WHERE create_date < GETDATE() - 1-- Days old

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @tname

WHILE @@FETCH_STATUS = 0
BEGIN
       SET @sql = 'DROP TABLE ' + @tname
       --EXEC (@sql)  -- For Executing
       PRINT @sql --For Printing

       FETCH NEXT FROM db_cursor INTO @tname
END

CLOSE db_cursor
DEALLOCATE db_cursor

Get SQL Server Physical Cores, Physical and Virtual CPUs, and Processor type information using Transact-SQL (T-SQL) scrip

• Total number of physical CPUs
• Total number of physical cores per CPUs
• Total number of physical cores
• Total number of virtual CPUs
• Processor type (x86 or x64)

As a result of this question, I wrote the following script using sys.dm_os_sys_info and xp_msver, which returns the required information:

exec xp_msver 'ProcessorCount'

Query Starts Here

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37

DECLARE @xp_msver TABLE (     [idx] [int] NULL     ,[c_name] [varchar](100) NULL     ,[int_val] [float] NULL     ,[c_val] [varchar](128) NULL     ) INSERT INTO @xp_msver EXEC ('[master]..[xp_msver]');; WITH [ProcessorInfo] AS (     SELECT ([cpu_count] / [hyperthread_ratio]) AS [number_of_physical_cpus]         ,CASE             WHEN hyperthread_ratio = cpu_count                 THEN cpu_count             ELSE (([cpu_count] - [hyperthread_ratio]) / ([cpu_count] / [hyperthread_ratio]))             END AS [number_of_cores_per_cpu]         ,CASE             WHEN hyperthread_ratio = cpu_count                 THEN cpu_count             ELSE ([cpu_count] / [hyperthread_ratio]) * (([cpu_count] - [hyperthread_ratio]) / ([cpu_count] / [hyperthread_ratio]))             END AS [total_number_of_cores]         ,[cpu_count] AS [number_of_virtual_cpus]         ,(             SELECT [c_val]             FROM @xp_msver             WHERE [c_name] = 'Platform'             ) AS [cpu_category]     FROM [sys].[dm_os_sys_info]     ) SELECT [number_of_physical_cpus]     ,[number_of_cores_per_cpu]     ,[total_number_of_cores]     ,[number_of_virtual_cpus]     ,LTRIM(RIGHT([cpu_category], CHARINDEX('x', [cpu_category]) - 1)) AS [cpu_category] FROM [ProcessorInfo]

The code of this script is tested on SQL Server 2005 and above versions.

Reference: https://basitaalishan.com/2014/01/22/get-sql-server-physical-cores-physical-and-virtual-cpus-and-processor-type-information-using-t-sql-script/