By: Daniel Farina | Last Updated: 2018-09-27 | Comments (4) | T-SQL
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.
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:
|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.
In order to invoke this stored procedure, you have to use the following syntax:
sp_rows [Table][, Order]
|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.
This example displays the number of rows as well as its space consumption for all tables whose name begins with “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.
- 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.
Last Updated: 2018-09-27
About the author
View all my tips