Guest Tips 2:SQL Server row count

Tip #2

Here's a little bit of script to count all rows on all tables.
This uses two very useful features of SQL Server sp_MSforeachtable and temporary tables (designated with a #).
sp_MSforeachtable is a System Stored Procedure (as you can see from the sp_ prefix) that performs an action for every user table in the database.
Any table whose name begins with # is local and temporary. This means it is only visible to the current session and will be dropped when the session ends.

create table #temp_tables
(
nr_of_rows numeric (15,4),
table_name varchar(40),
)
go
exec sp_MSforeachtable 'insert into #temp_tables select count(*) as nr_of_rows, ''?'' as table_name from ?'
go
select * from #temp_tables
drop #temp_tables