MS SQL 2005 (T-SQL) Row Count for Each Table

At work I needed a solution to give me a count of how many rows each table contained. I’ve always liked phpMyAdmin’s ability to list all the tables and show their size and row count. I’ve found it immensely helpful. However, I couldn’t find anything similar for SQL Server Manager Studio 2005. I found a blog post that pointed me in the right direction on how to get the row counts, but I couldn’t get their script to run on the SQL Server 2005. Since I figured this would be a useful script to have I would re-write it and comment it for others. Here it is:

-- Select Database
-- This should be the only configuration you need
USE Your_Database
GO

-- Create a cursor to loop through the System Ojects and get each table name
DECLARE TBL_CURSOR CURSOR
-- Declare the SQL Statement to cursor through
FOR ( SELECT Name FROM Sysobjects WHERE Type='U' )

-- Declare the @SQL Variable which will hold our dynamic sql
DECLARE @SQL NVARCHAR(MAX);
SET @SQL = '';
-- Declare the @TblName Variable which will hold the name of the current table
DECLARE @TblName NVARCHAR(MAX);

-- Open the Cursor
OPEN TBL_CURSOR

-- Setup the Fetch While that will loop through our cursor and set @TblName
FETCH NEXT FROM TBL_CURSOR INTO @TblName
-- Do this while we are not at the end of the record set
WHILE (@@FETCH_STATUS <> -1)
BEGIN
-- Appeand this table's select count statement to our sql variable
SET @SQL = @SQL + ' ( SELECT '''+@TblName+''' AS Table_Name,COUNT(*) AS Count FROM '+@TblName+' ) UNION';

-- Pull the next record
FETCH NEXT FROM TBL_CURSOR INTO @TblName
-- End the Cursor Loop
END

-- Close and Clean Up the Cursor
CLOSE TBL_CURSOR
DEALLOCATE TBL_CURSOR

-- Since we were adding the UNION at the end of each part, the last query will have
-- an extra UNION. Lets  trim it off.
SET @SQL = LEFT(@SQL,LEN(@SQL)-6);

-- Lets do an Order By. You can pick between Count and Table Name by picking which
-- line to execute below.
SET @SQL = @SQL + ' ORDER BY Count';
--SET @SQL = @SQL + ' ORDER BY Table_Name';

-- Now that our Dynamic SQL statement is ready, lets execute it.
EXEC (@SQL);
GO

How it works

A basic description would be it gets a listing of each table inside the the System Objects table and creates a Dynamic SQL Statement that get a count from each table and return a record set with the Table’s name and count. Pretty straight forward, and I commented just about every little thing in the script so it should be easy to follow along. Let me know if you have any suggestions or tips for making it better. Thanks!

Justin is currently the Director of Development for the Deseret News. He is active in the Utah Open Source community. He is an advisory member of the Utah Open Source Foundation, and helps with the anual Utah Open Source Conference. He primarily focuses on PHP, MySQL, Redis, HTML, CSS, jQuery, and JavaScript. When he gets the time, he enjoys to play jazz piano. Read More

Tagged with: , , , ,
Posted in Programming