Cry How to...
Count the total number of records in all user tables
I once had a management request for the total number of records in a database. This may not be a particularly useful statistic, but ...
To count the number of records in a table:
select count(*) from <table-name>
To count the number of user-tables:
select count(*) from sysobjects where type='U'
To count the total number of records in all the user tables (this uses cursors to loop through considering each table at a time):
declare table_cursor CURSOR for select name from sysobjects where type='U'
declare @table_name sysname
declare @num_recs int
declare @count_sql as nvarchar(128)
declare @total_recs int
set @total_recs = 0
open table_cursor
fetch next from table_cursor into @table_name
while @@FETCH_STATUS=0
begin
set @count_sql = 'select @num_recs = count(*) from ' + @table_name
exec sp_executesql @count_sql,N'@num_recs int output',@num_recs output
print @table_name + ' ' + cast(@num_recs as varchar)
set @total_recs = @total_recs + @num_recs
fetch next from table_cursor into @table_name
end
close table_cursor
deallocate table_cursor
print 'Total number of records: ' + cast(@total_recs as varchar)
These notes have been tested against SQL Server 2000.
About the author: Brian Cryer is a dedicated software developer and webmaster. For his day job he develops websites and desktop applications as well as providing IT services. He moonlights as a technical author and consultant.