SQL Server Portal

Whenever we migrate the database, we need to have some handy tools that can help us to verify our migrated data and can reduce the time needed to verify it. Today, I will share a script that will not only help you with database migration, but also it can help you with routine tasks. I usually use that tool to find if any master table is empty or not in the migrated data.

Given below is the script.

--This script is compatible with SQL Server 2005 and above.
USE AdventureWorks2012
GO
SELECT
OBJECT_SCHEMA_NAME(OBJECT_ID) AS [Schema Name]
, OBJECT_NAME(OBJECT_ID) As [Table Name]
, SUM([rows]) as [Total Records] FROM sys.partitions
WHERE OBJECTPROPERTYEX (OBJECT_ID, N'IsUserTable')=1
--Remove the above where clause,
--if you need to view system objects as well.
AND [index_id] <=1
GROUP BY OBJECT_ID
HAVING SUM([rows])=0
ORDER BY OBJECT_NAME(OBJECT_ID)
--OUTPUT