Get SQL Server Row Count and Space Used with the sp_rows

Problem

One task we often have with SQL Server is determining how many rows are in a table and how much space a table is using.  We can do this using sp_spaceused, but this is a little bit cumbersome to get details for all tables or to sort the data in a particular way.  In this tip I will show you a stored procedure that returns the same information of sp_spaceused, but for all the tables and can be used as an improved version of sp_spaceused.

Solution

One of the most basic tasks of a SQL Server Database Administrator (DBA) is to monitor and deal with disk space. Specifically, as SQL Server DBAs must be aware of free disk space for our databases files; free space of each database file (both data and log files) and also the size of the biggest tables.  We can use this information to partition tables or move the data to another file or filegroup by performing a rebuild of the table.

The sp_rows SQL Server Stored Procedure

First and foremost, I must tell you that although this stored procedure starts with “sp_”, it is not a system stored procedure. In fact, it is a user stored procedure that was created by Jarlath O’Grady (https://www.linkedin.com/in/jarlathogrady/ ) back in the SQL Server 6.5 days. The fact that he named the stored procedure starting with “sp_” is because when we create this stored procedure in the master database, it can behave and be invoked as a system stored procedure. In other words, you can execute this stored procedure in the context of any database and it will be executed like if the stored procedure is in the current database.

I used this stored procedure on a job I had in the past. Unfortunately, I didn’t save a copy of the code. Since it was not proprietary code I searched on the internet and end up finding the following document which is the instructions to use the procedure https://www.databasejournal.com/img/sp_rows.rtf, but not the code.  With the aid of that document, I reconstructed Jarlath’s code.

The sp_rows stored procedure can be used for a quick view of:

• The row count for all user tables within a database
• The row count for an individual table
• The row count for tables with LIKE table names

As I previously mentioned, this procedure is based on the system stored procedure sp_spaceused, therefore the result set will be very familiar to you. Take a look at the next screen capture.

Let’s describe each column:

The original Jarlath’s code had the option to include system tables in the results. In order to make things easier, I decided to not include system tables. Mostly because on versions of SQL Server newer than 2005 the system tables can’t be queried by the user.

I decided to add a feature which is the ability to select how you want the result to be sorted.

The following is the code of my interpretations of Jarlath’s idea.

USE Master;
GO;
CREATE PROCEDURE dbo.sp_rows
    @filter AS sysname = NULL ,
    @order AS INT = 1
AS
    DECLARE @sql AS NVARCHAR(1000)
    DECLARE @descending AS BIT = 0
    CREATE TABLE #Temp1
        (
          name sysname ,
          rows BIGINT ,
          reserved NVARCHAR(80) ,
          data NVARCHAR(80) ,
          index_size NVARCHAR(80) ,
          unused NVARCHAR(80)
        );
    INSERT INTO #Temp1
            ( name ,
              rows ,
              reserved ,
              data ,
              index_size ,
              unused
            )
            EXEC sp_MSforeachtable 'exec sp_spaceused [?]'
    CREATE TABLE #Temp2
        (
          name sysname ,
          rows BIGINT ,
          reserved BIGINT ,
          data BIGINT ,
          index_size BIGINT ,
          unused BIGINT
        );
    INSERT INTO #Temp2
            ( name ,
              rows ,
              reserved ,
              data ,
              index_size ,
              unused
            )
            SELECT  name ,
                    rows ,
                    LEFT(reserved, LEN(reserved) - 3) ,
                    LEFT(data, LEN(data) - 3) ,
                    LEFT(index_size, LEN(index_size) - 3) ,
                    LEFT(unused, LEN(unused) - 3)
            FROM    #Temp1
-- Check boundaries
    IF ABS(@order) > 6
        OR @order = 0
        BEGIN
            SET @order = 1
        END
-- Setting Ascending or Descending order
    IF @order < 0
        BEGIN
            SET @descending = 1
        END

    SET @sql = 'SELECT name ,
       rows ,
       reserved AS [reserved KB],
       data AS [data KB],
       index_size AS [index_size KB],
       unused AS [unused KB] 
        FROM #Temp2
      WHERE name LIKE ''' + ISNULL(@filter, '') + '%'' 
      ORDER BY ' + CAST(ABS(@order) AS NVARCHAR(10))
        + CASE WHEN @descending = 1 THEN ' DESC'
               ELSE ''
          END
    EXEC sp_executesql @sql
    DROP TABLE #Temp1
    DROP TABLE #Temp2
GO

As you may see the code is very simple. I am taking advantage of the undocumented system stored procedure sp_MSforeachtable which runs a specific command on each table of a given database.

Syntax

In order to invoke this stored procedure, you have to use the following syntax:

sp_rows [Table][, Order]

Something to note is that the table name in most cases doesn’t need to be surrounded with quotes.

Example Usage

This example displays the number of rows as well as its space consumption for all tables whose name begins with “emplo”.

sp_rows emplo

The following example displays the number of rows as well as its space consumption for all tables ordered by reserved unused size in descending order.

sp_rows NULL, -3

The following example displays the number of rows as well as its space consumption for all tables ordered by unused size in descending order.

sp_rows NULL, -6

The following example displays the number of rows as well as its space consumption for all tables ordered by the number of rows in ascending order.

Next Steps

• If you like the code, you can mark this stored procedure as a system stored procedure. This will prevent accidental deletion. You can learn how to do this in this tip: Creating Your Own SQL Server System Stored Procedures.
• Do you hear on your career that the sp_ prefix shouldn’t be used? Want to know why and when it’s ok to be used? Take a look at the following tip: SQL Server sp_ prefix when and when not to use it.
• Since prevention is better than cure, here I give you a tip to read when I show you how to create a report of disk space. Of course you can try to use that template and create your own report disk space by table or any other of the possibilities that give us sp_rows stored procedure: Create SQL Server Disk Space Report for All Servers. Comment if you want me to write a tip about that!
• For more tips about database administration take a look at SQL Server Database Administration Tips.