Dot Net Technologies -- MS Tech talk and more - Tip of the Day: Getting Table Counts

Wednesday, September 02, 2009

« Tip of the Day: sp_findsp revisted | Main | Starting new projects.. how to approach ... »

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