Script to get row count for all tables in a SQL Server database

This post helps you in writing a Script to get row count for all tables in a SQL Server database. For any database developer or administrator the most common requirement is to identifying the larger tables based on data size, index size and row count. This script can quickly list out all tables in the given database and retrieves the below details:

• Name: Table Name
• Rows: Total row count
• Reserved: Space reserved for the table – MB
• Data: Total space allocated for data pages – MB
• Index_size: Total space allocated for Index pages – MB
• Unused: Unused space – MB

Here is the script to get row count for all tables in a SQL Server database:

SET NOCOUNT ON;
BEGIN TRY
	--Create a temparory table 
	CREATE TABLE #Tab (
			[Name]		 NVARCHAR(128),    
			[Rows]		 CHAR(11),    
			[Reserved]	 VARCHAR(18),  
			[Data]		 VARCHAR(18),     
			[Index_size] VARCHAR(18),    
			[Unused]	 VARCHAR(18)); 

	--Capture all tables data allocation information 
	INSERT #Tab 
	EXEC sp_msForEachTable 'EXEC sp_spaceused ''?''' ;

	--Alter Rows column datatype to BIGINT to get the result in sorted order
	ALTER TABLE #Tab ALTER COLUMN [ROWS] BIGINT  ;

	-- Get the final result: Remove KB and convert it into MB
	SELECT	Name,
		[Rows],
		CAST(LTRIM(RTRIM(REPLACE(Reserved,'KB',''))) AS BIGINT)/1024.0 AS 'Reserved MB',
		CAST(LTRIM(RTRIM(REPLACE(Data,'KB',''))) AS BIGINT)/1024.0 AS 'Data MB',
		CAST(LTRIM(RTRIM(REPLACE(Index_Size,'KB',''))) AS BIGINT)/1024.0 AS 'Index_Size MB',
		CAST(LTRIM(RTRIM(REPLACE(Unused,'KB',''))) AS BIGINT)/1024.0 AS 'Unused MB'
	FROM #Tab 
	ORDER BY [rows] DESC;

END TRY
BEGIN CATCH
	DROP TABLE #Tab; 
END CATCH
-- Drop the temparory table 
DROP TABLE #Tab; 

When you need to get the list of tables along with the row counts, connect to the database instance open a new query window, paste the above script and execute it. It shouldn’t take much time to execute the script as we are using “sp_spaceused”, for us it took maximum 6 sec on a 3.5 TB database.

Remember there are chances where sp_spaced might give the wrong row counts due to several reasons. There are mainly 3 reasons that cause sp_spaceused gives us the wrong counts:

• When there are huge DDL changes
• Statistics are not updated
• Index maintenance is not being taken care

Here you will find more details and resolution.