Get Row Count from all tables in a MSSQL Database

We are in the middle of a migration of a large database from one Azure region to another. Having configured the geo-replication I wanted to confirm the replication is working correctly. Yes I am paranoid like that.

So a few searches later landed at this awesome page on MSDN (https://msdn.microsoft.com/en-us/library/ms190324.aspx). And managed to put together this little script. There could be better ways of doing this, but this one worked for our needs.

Happy to see how you are doing it

-- Run this against the database from which you want the row counts
-- Ref https://msdn.microsoft.com/en-us/library/ms190324.aspx
SELECT  
      QUOTENAME(SCHEMA_NAME(sysObjects.schema_id)) + '.' + QUOTENAME(sysObjects.name) AS [Table]
      , SUM(sysPartitions.Rows) AS [Rows]
FROM  
      sys.objects AS sysObjects
      INNER JOIN sys.partitions AS sysPartitions
            ON sysObjects.object_id = sysPartitions.object_id
WHERE  
      sysObjects.type = 'U' -- U = Table (user-defined) (https://msdn.microsoft.com/en-us/library/ms190324.aspx)
      AND sysObjects.is_ms_shipped = 0x0 --Object is created by an internal SQL Server component. Ignore these objects from the query
      AND index_id < 2 -- We need all Heap (0) and Clustered (1) indices
GROUP BY  
      sysObjects.schema_id, sysObjects.name
ORDER BY [Table]  
GO

Also available at a gist here (https://gist.github.com/sarangbk/01549440ef73e8729b19baf0608c08c8) for some reason Gist editor is not happy to syntax highlight any SQL code today so not including the embed here.

Menu