Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
How to survive the next Azure outage    ===>    Register Now
 

Get SQL Server Row Count and Space Used with the sp_rows


By:   |   Read Comments (4)   |   Related Tips: More > T-SQL

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.

sp_rows output example

Let’s describe each column:

Column Description
Name This is the name of the table.
Rows Number of rows existing in the table.
Reserved Total amount of reserved space for the table.
Data Total amount of space used by data in the table.
Index_size Total amount of space used by indexes in the table
Unused Total amount of space reserved for the table that is not being used.

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]
Parameter Description
Table This is the name of the table or the first letters of the name. This parameter is optional and when not specified sp_rows will return all the tables.
Order Number of the column you want to get the output sorted. It can be any of the following numbers:

1 = Order by Table Name.
2 = Order by Table Rows.
3 = Order by reserved space.
4 = Order by space used by data in the table.
5 = Order by space used by indexes in the table.
6 = Order by the space reserved for the table that is not being used.

Additionally you can add the – sign in front of the number in case you want the data sorted in descending 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
sp_rows output example

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
sp_rows output example

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
sp_rows output example

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.

sp_rows output example
Next Steps


Last Update:


next webcast button


next tip button



About the author
MSSQLTips author Daniel Farina Daniel Farina was born in Buenos Aires, Argentina. Self-educated, since childhood he showed a passion for learning.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Wednesday, October 10, 2018 - 10:22:07 AM - Addisu Mekonnen Back To Top

I have modified the initial script. The new Script has a new parameter '@dbname' which is a database name you can pass to the stored procedure and you can get information about the tables in that DB.

USE Master
GO
CREATE PROCEDURE usp_rows @dbname AS sysname=NULL,
    @filter AS sysname = NULL ,
    @order AS INT = 1
AS
    DECLARE @sql AS NVARCHAR(1000)
    DECLARE @descending AS BIT = 0
SET @sql=QUOTENAME(@dbname)+'.sys.sp_MSforeachtable ''exec sp_spaceused [?]'''
--PRINT @sql
    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 (@sql)
            --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

Tuesday, October 02, 2018 - 11:53:33 PM - Daniel Farina Back To Top

Hi Jochen Trump,

Hope this message finds you well.

In this tip I just wanted to refer to Jarlath O’Grady's original idea of sp_rows. Please take into consideration that when he made his code (SQL Server 6.5 days) there wasn't schema names to take about. If you want that I write another tip with a code that covers shema names please let me know and I will happily write it!
Thank you so much for reading! Best regards!


Monday, October 01, 2018 - 2:00:00 AM - Igor Micev Back To Top

What if I create the stored procedure in another database than master? It doesn't work. Many DBAs have separate databases where they keep such stored procedures. 

So, this doesn't work for all. You should re-work it.


Thursday, September 27, 2018 - 4:12:11 AM - Jochen Trump Back To Top

Good solution, but it should include schema_name

Best,
Jochen


Learn more about SQL Server tools