I can't take credit for this, as I took it from the article How To Get Table Row Counts Quickly And Painlessly on SqlServerCentral.com by Kendal Van Dyke. However, it such a good tip, I had to change it a bit, as I tend to use this a LOT.
First, I made the results a little more "printable" but reducing the table name column. Next, I made it a system proc because when I go onto client sites, I am looking at many databases. I probably don't need to create a procedure for it, but there have been many cases where I trend things, and it would be nice to have this handy. Finally, I added a parameter to allow me to change the sorting. By default, it sorts by table name. I added an optional parameter to allow me to pass in an integer (could have done it a million ways, I know, but I chose to do this solely for my preferences -- feel free to change to yours).
Usage: sp_retrieveRowCounts --- sorts by table name
sp_retrieveRowCounts 1 ---sorts by row count, then table name
Here's the proc:
USE master
GO
IF (SELECT OBJECT_ID('sp_retrieveRowCounts','P')) IS NOT NULL
BEGIN
DROP PROC sp_retrieveRowCounts
END
GO
CREATE PROC sp_retrieveRowCounts (@OrderByCount int = null)
AS
BEGIN
DECLARE @OrderByParm int
DECLARE @SQLStatement varchar(700)
DECLARE @OrderByClause varchar(35)
SELECT @OrderByParm = ISNULL(@OrderByCount,0)
IF (@OrderByParm != 0)
SELECT @OrderByClause = ' ddps.row_count DESC, o.NAME ASC'
ELSE
SELECT @OrderByClause = ' o.NAME ASC'
SELECT @SQLStatement = 'SELECT TableName = LEFT(o.name,50), TotalRows = ddps.row_count FROM sys.indexes AS i
INNER JOIN sys.objects AS o ON i.OBJECT_ID = o.OBJECT_ID
INNER JOIN 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.is_ms_shipped = 0
ORDER BY ' + @OrderByClause
EXEC(@SQLStatement)
END
--Mark procedure as system object
EXEC sys.sp_MS_marksystemobject sp_retrieveRowCounts
GO
GRANT EXEC ON sp_retrieveRowCounts TO public
GO