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.