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!
Related Posts
- MySQL - Does Table Exist w/o Throwing Errors There are times where you would like to know if a table exists before executing an query. Most solutions require having MySQL throw an error saying "table does not exist," but I prefer a cleaner way. I found on this forum post a clean way to do it: PLAIN TEXT...
- MySQL & PHP - SQL_CALC_FOUND_ROWS - An easy way to get the total number of rows regardless of LIMIT Here is a little trick I found awhile back. I faced a challenge several months ago where I had to query a large, complex data result from MySQL. I "paged" through the results using LIMIT and OFFSET. However, I wanted to know the total number of rows w/o the LIMIT....
- PHP Design - Biggest Database Oversights Over the last three years I've had the opportunity to work on several PHP projects, some of them having grown rapidly and required to scale quickly. Three in particular have been a fantastic learning experience for me. Now I don't consider myself a total expert, but I thought I would...
replaced cursor with a table vriable & a while loop. More efficient , though not noticable with small datasets such as these:
——-
– Select Database
– This should be the only configuration you need
USE Testing
GO
declare @Tables table (id integer identity(1,1),TName varchar(max))
insert into @Tables (TName) SELECT Name FROM Sysobjects WHERE Type=’U’
– Declare the @SQL Variable which will hold our dynamic sql
DECLARE @SQL NVARCHAR(MAX);
Declare @counter integer
– Declare the @TblName Variable which will hold the name of the current table
DECLARE @TblName NVARCHAR(MAX);
SET @SQL = ”;
select @counter = 1
While @counter <= (select max(id) from @Tables)
Begin–While Loop
select @TblName = (select TName from @Tables where id = @Counter)
– 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’;
select @counter = @counter+1
End–While Loop
– 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
——-
try this
use yourdatabasename
SELECT name, rows FROM sysindexes where impid < 0 order by name
The sysindexes query is fine but relies upon your database stats being up-to-date.
Also I found that square brackets were needed around the tablename on line 26 - in the dynamic query (some of my tables begin with a numeric character)
instead of:
SET @SQL = @SQL + ‘ ( SELECT ”’+@TblName+”’ AS Table_Name,COUNT(*) AS Count FROM ‘+@TblName+’ ) UNION’
use:
SET @SQL = @SQL + ‘ ( SELECT ”’+@TblName+”’ AS Table_Name,COUNT(*) AS Count FROM ['+@TblName+'] ) UNION’
Wouldn’t this be easier?!
SELECT ’select ”’+ name + ”’, count(*) from ‘ + name + ‘ union’
FROM Sysobjects
WHERE Type=’U’
ORDER BY 1;
Copy + paste the results, remove the trailing ‘union’ and add your ‘order by’ clause.