T-SQL Stored Procedure to Get the Rough Row Count(s) of Specific Table(s)

If you’d like to get the most accurate of row count of any table, select count(*) is still your BFF. However, in some situations, the speed of retrieving a rough estimate of the row count from a very large table is more emphasized upon. And hence here goes the script:

/*
.Synopsis
Stored procedure to (quickly) get the rough estimate(s) of row count(s) for specific table(s).

The accuracy of row counts depends on how often the stats update is run on the target instance.

.Original link
http://www.sqlservercentral.com/articles/T-SQL/67624/
http://www.brentozar.com/archive/2014/02/count-number-rows-table-sql-server/


.SQL Version
2005+

.Last Modified
2014-03-19 by Nick Xu

.Example
EXEC sp__GetTableRowCount @dbname = 'AdventureWorks'

*/


--For generic setting
USE master
GO

IF EXISTS (SELECT * FROM sysobjects WHERE name = N'sp__GetTemplateProc' AND type = N'P')
BEGIN
	DROP PROC sp__GetTableRowCount
END
GO


CREATE PROC sp__GetTableRowCount
@dbname VARCHAR(100) = 'master', @tablename VARCHAR(100) = NULL
AS
BEGIN

	-- Shows all user tables and row counts for the current database 
	-- Remove is_ms_shipped = 0 check to include system objects 
	-- i.index_id < 2 indicates clustered index (1) or hash table (0) 
	
	DECLARE @sql AS VARCHAR(1000)
	
	
	IF @tablename IS NULL
	BEGIN
		SET @sql = 
		'SELECT '''+ @dbname + ''' as [DBName], o.name AS [TableName], ddps.row_count AS [RowCount] 
		FROM [' + @dbname + '].sys.indexes AS i 
		INNER JOIN [' + @dbname + '].sys.objects AS o ON i.OBJECT_ID = o.OBJECT_ID 
		INNER JOIN [' + @dbname + '].sys.dm_db_partition_stats AS ddps ON i.OBJECT_ID = ddps.OBJECT_ID 
		AND i.index_id = ddps.index_id 
		WHERE i.index_id < 2 
		ORDER BY [RowCount] DESC'
		--SELECT @sql
		EXEC(@sql)
	END
	ELSE
	BEGIN
		SET @sql = 
		'SELECT '''+ @dbname + ''' as [DBName], o.name AS [TableName], ddps.row_count AS [RowCount] 
		FROM [' + @dbname + '].sys.indexes AS i 
		INNER JOIN [' + @dbname + '].sys.objects AS o ON i.OBJECT_ID = o.OBJECT_ID 
		INNER JOIN [' + @dbname + '].sys.dm_db_partition_stats AS ddps ON i.OBJECT_ID = ddps.OBJECT_ID 
		AND i.index_id = ddps.index_id 
		WHERE i.index_id < 2 AND o.name= ''' + @tablename + '''' 
		--SELECT @sql
		EXEC(@sql)
	END
END
GO


Advertisements

About Nick Xu

Not a DBA anymore
This entry was posted in SQL Server, T-SQL Script and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s