Determining # Rows In Every Table

This article was actually inspired by a post in the SQL Server public newsgroups and the solutions that I gave for them. Assume that you are doing an automated performance testing on your application that talks to a database. During this performance testing your application would be constantly adding rows to the database and let's assume that you need to monitor the changes in the tables in terms of the number of rows added. How do you do it?? This article will explore how, using two methods.

Method A, which uses SQL Server documented features.
Method B, which uses SQL Server undocumented features.

Note that it is never recommended to use an undocumented feature, since Microsoft has solid reasons for making something un-documented. Mostly the reasons are that un-documented features will not be supported in case you run into problems, nor are they guaranteed to be supported in future relases or service packs. Thus, venture into un-documented features at your own risk!! But, believe me, sometimes it is worth it :-)

Method A

In the first method, we use documented methods to determine the number of rows in each table in a database. The logic is quite simple. We loop through each table in the database and execute the COUNT(*) command which will return the count of the number of rows. Here is the code for the procedure that does this.


IF (OBJECT_ID ('dbo.GetAllTablesRowCount') IS NOT NULL)
	DROP PROCEDURE dbo.GetAllTablesRowCount
GO
CREATE PROCEDURE dbo.GetAllTablesRowCount AS
BEGIN
	SET NOCOUNT ON

	-- Declare a cursor that gets a list of all the tables
	-- in the database
	DECLARE tablesList CURSOR FOR
		SELECT table_name FROM information_schema.tables
			WHERE table_type = 'BASE TABLE'
			ORDER BY table_name

	-- Scratch variables used in the program
	DECLARE @tableCount INT
	DECLARE @lcTableName VARCHAR(100)
	DECLARE @sqlString NVARCHAR(1000)

	-- Output table that contains all the results
	CREATE TABLE #tableCounts (tableName VARCHAR(100), recordCount INT)
	
	-- Open the cursor and loop through it
	OPEN tablesList
	FETCH NEXT FROM tablesList INTO @lcTableName
	WHILE (@@FETCH_STATUS = 0)
	BEGIN
		-- From the command to execute against each table and execute
		-- the same
		SET @sqlString = 'SELECT @rowCount = COUNT(*) FROM ' + @lcTableName
		EXEC sp_executesql @sqlString, N'@rowCount INT OUTPUT', @tableCount OUTPUT

		-- Insert the result into the result table	
		INSERT INTO #tableCounts VALUES (@lcTableName, @tableCount)
		FETCH NEXT FROM tablesList INTO @lcTableName
	END
	-- Cleanup the cursor
	CLOSE tablesList
	DEALLOCATE tablesList

	-- Return the result and the temporary tables automatically
	-- goes out of scope	
	SELECT * FROM #tableCounts ORDER BY tableName
END
GO

The crux of the code is the loop of the cursor that iterates through each table. Note that we use the information_schema.tables view rather than the sysobjects table. Using the information schema views is a better option since they are guaranteed not to be changed (whereas the sysobjects can undergo some change that can break your code). You can now execute the code like this and the output will be as shown (I've run this code against the pubs database):


EXEC dbo.GetAllTablesRowCount


tableName          recordCount 
------------------ ----------- 
authors            23
discounts          3
employee           43
img                2
jobs               14
pub_info           8
publishers         8
roysched           86
sales              21
samp_test          4
stores             6
titleauthor        25
titles             18

Well that was quite simple! You can now execute this code from an external application and monitor the changes.

Method B

The next method involves using an un-documented stored procedure called sp_MSforeachtable. You can find the source code for this procedure in the master database. This stored procedure executes upto 3 commands for every table in the database (with an optional where clause). Each table defaults to its own result set. You can also execute a pre-command and a post-command that can be used to perform some setup and cleanup tasks. Here is the GetAllTablesRowCount stored procedure re-written using the sp_MSforeachtable command.


IF (OBJECT_ID ('dbo.GetAllTablesRowCount') IS NOT NULL)
	DROP PROCEDURE dbo.GetAllTablesRowCount
GO
CREATE PROCEDURE dbo.GetAllTablesRowCount AS
BEGIN
	SET NOCOUNT ON

	-- Create a temporary table to hold the result set
	CREATE TABLE #Temp (tableName VARCHAR(100), recordCount INT)

	-- Execute the un-documented procedure that automatically
	-- iterates through each table and executes the specified
	-- command
	EXEC sp_MSforeachtable
		@command1 = 'INSERT INTO #Temp SELECT ''?'', COUNT(*) FROM ?'


	-- Return the result
	SELECT * FROM #Temp ORDER BY tableName
END

The highlighted portion shows the usage of the command. Note that we have used only one command parameter (called command1). The ? acts like the wild-card character and will be substituted with the name of the table, complete with the user-name. You can specify your own wild-card character using the @replaceChar parameter to the procedure as shown:


EXEC sp_MSforeachtable
	@command1 = 'INSERT INTO #Temp SELECT ''~'', COUNT(*) FROM ~',
	@replaceChar = '~'

Now, when you execute the dbo.GetAllTablesRowCount, you will see the following output:


EXEC dbo.GetAllTablesRowCount
GO



tableName                  recordCount 
-------------------------- ----------- 
[dbo].[authors]            23
[dbo].[discounts]          3
[dbo].[employee]           43
[dbo].[img]                2
[dbo].[jobs]               14
[dbo].[pub_info]           8
[dbo].[publishers]         8
[dbo].[roysched]           86
[dbo].[sales]              21
[dbo].[samp_test]          4
[dbo].[stores]             6
[dbo].[titleauthor]        25
[dbo].[titles]             18

You can see that the output is similar to what we had earlier via Method A. So which method will you use?? You can actually use both methods, but I prefer Method A since it uses documented features, but you could equally well use Method B, provided you are aware of the pitfalls using un-documented procedures. The advantage of using the un-documented procedure is that you need not write the cursor code, but fact remains that you need to see the source code first to understand what the parameters are, for the procedure.

You can write many useful utility procedures using the methods outlined in this article. For example, you can monitor the space allocated to each table during a performance test, or list all the contents of every table. Have fun!!

Home