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
- ASP .NET & System.Threading.ThreadAbortException Error While doing some work today I started to run into a ThreadAbortException error. While debugging it spit back this error: Unable to evaluate expression because the code is optimized or a native frame is on top of the call stack. I was really confused because everything seemed to be working...
- PHP5.x or PHP6 – Argument for Type-Hinting: Better IDEs I’ve read some posting lately about PHP6, the Meeting Notes by the Developers, etc. that deal with type-hinting, especially scalar type hinting. Here is my reasoning why PHP should allow for more type-hinting scenarios: it allows IDEs to be more powerful. An example would be auto completion. Many times an...
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.
Great post. Only issue I had was that my tables all use different schemas and not the default “dbo” schema, so I had to join to the sys.schemas table on schema_id and then concatenate the two columns from the sys.objects and sys.schemas tables together to get it to work.
Quick question: Is there a way to list the row counts as well as the table sizes?
Nice little post for this. I tweaked it for SQl Server 2000 and its limitation on varchar size. Left the cursor use in:
– 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 VARCHAR(8000), @SQL2 VARCHAR(8000), @SQL3 VARCHAR(8000)
SET @SQL = ”;
SET @SQL2 = ”;
SET @SQL3 = ”;
– Declare the @TblName Variable which will hold the name of the current table
DECLARE @TblName VARCHAR(255);
– 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
IF ASCII (@TblName) % 3 = 0
SET @SQL = @SQL + ‘ ( SELECT ”’+@TblName+”’ AS Table_Name, COUNT(*) AS Count FROM ‘+@TblName+’ ) UNION’;
IF ASCII (@TblName) % 3 = 1
SET @SQL2 = @SQL2 + ‘ ( SELECT ”’+@TblName+”’ AS Table_Name, COUNT(*) AS Count FROM ‘+@TblName+’ ) UNION’;
IF ASCII (@TblName) % 3 = 2
SET @SQL3 = @SQL3 + ‘ ( 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);
–SET @SQL2 = LEFT(@SQL2, LEN(@SQL2)-6);
SET @SQL3 = LEFT(@SQL3, LEN(@SQL3)-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 + @SQL2 + @SQL3 + ‘ ORDER BY Count’);
–EXEC (@SQL + @SQL2 + @SQL3 + ‘ ORDER BY Table_Name’);
GO
I used this:
SELECT object_name(id) ,rowcnt
FROM sysindexes
WHERE indid IN (1,0) AND OBJECTPROPERTY(id, ‘IsUserTable’) = 1
order by rowcnt desc
Hi Justin Carmony,
I am an Oracle DBA and I wanted something in SQL Server 2005 that is similar to
PROCEDURE GET_STAGING_INSERT_COUNTS
IS
v_ErrorCode integer;
v_ErrorMessage varchar2(512);
v_table_name varchar2(100);
e_countfailure EXCEPTION;
BEGIN
.
.
.
for c1rec0 in (select table_name,
to_number(extractvalue(xmltype(dbms_xmlgen.getxml(‘select count(*) c from ‘ ||
table_name)),
‘/ROWSET/ROW/C’)) ROW_count
from user_tables ttl
) loop
begin
insert_log_data(c1rec0.table_name,
to_char(sysdate, ‘YYYYMMDDHH24MISS’),
‘STG COUNT’, ‘Success’, c1rec0.ROW_count,
‘from staging’);
COMMIT;
end;
.
.
.
end loop;
.
.
.
COMMIT;
EXCEPTION
WHEN others THEN
return_status := ‘Failed’;
v_errorcode := SQLCODE;
v_errormessage := sqlerrm;
insert_ATOS_log_data(v_table_name, to_char(sysdate, ‘YYYYMMDDHH24MISS’), ‘COUNTS’, ‘Failed’, 0, ‘GET_STAGING_INSERT_COUNTS’);
insert_ATOS_log_data(‘Process Failed’, to_char(sysdate, ‘YYYYMMDDHH24MISS’), ‘COUNTS’, ‘Failed’, 0, ‘Load_Control Error Code: ‘ || v_errorcode || ‘ Error Message: ‘ || v_errormessage);
END GET_STAGING_INSERT_COUNTS;
and the above will save me a tremendous amount of time.
Thank You
CREATE TABLE DB_TBL_ROWCNT (T_NAME VARCHAR(100), R_COUNT INTEGER)
insert into db_tab exec sp_tables
select ‘select ”’+ table_name +”’ ,count(*) from ‘+ table_name + ‘ union’
from db_tab where table_type=’TABLE’
–(output of above query ) –> [A]
INSERT INTO DB_TBL_ROWCNT
–>[A]
select * from DB_TBL_ROWCNT
Jus another way…
CREATE TABLE DB_TAB (TABLE_QUALIFIER VARCHAR(50),TABLE_OWNER VARCHAR(50), TABLE_NAME VARCHAR(100), TABLE_TYPE VARCHAR(50), REMARKS VARCHAR(50))
CREATE TABLE DB_TBL_ROWCNT (T_NAME VARCHAR(100), R_COUNT INTEGER)
insert into db_tab exec sp_tables
select ‘select ”’+ table_name +”’ ,count(*) from ‘+ table_name + ‘ union’
from db_tab where table_type=’TABLE’
–(output of above query ) –> [A]
INSERT INTO DB_TBL_ROWCNT
–>[A] (remove the last “union” here)
select * from DB_TBL_ROWCNT
Thnx,
Ram
exec sp_msforeachtable “select ‘?’ tablename, sum(1) totalrows from ?”
– I used this:
select o.name, i.rows, i.indid –, i.*
from sysindexes i, sysobjects o
where i.indid < 2
and o.id = i.id
and o.type = 'u'
order by 1