How Many Rows in a Table?
Have you ever wondered how many rows your SQL Server database tables contains?
A few times now I've wanted to grab a list of table names with the row count for each table. In a post about a few undocumented Stored Procedures I came across this neat script.
EXEC sp_MSforeachtable 'SELECT ''?'', Count(*) as NumberOfRows FROM ?'
It uses an undocumented stored procedure that iterates over all user tables in the database. The only issue I had with it, was that the output was really tedious to read. I wanted a simple tabular display showing me a list of tables with their name and number of rows, but sorted in descending order by the number of rows. Note that you can get this information in Management Studio
Here's a short script that does that...
if(object_id ('tempdb.dbo.#TableRowCount') is not null)
drop table #TableRowCount
go
create table #TableRowCount
(Id int identity(1,1) NOT NULL primary key
,TableName nvarchar(100)
,RowsInTable int)
insert into #TableRowCount (TableName, RowsInTable)
exec sp_MSforeachtable 'SELECT ''?'', Count(*) as NumberOfRows FROM ?'
select * from #TableRowCount order by RowsInTable desc
Alternatively you can use the following script (which is what Management Studio uses)
select
[name] AS TableName
,TableRowCount = (
select sum (spart.rows) from sys.partitions spart
where spart.object_id = tbl.object_id and spart.index_id < 2 )
from sys.tables tbl
order by TableRowCount desc
Which do you prefer?