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:

[sourcecode language=”sql”]
/*
.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

[/sourcecode]