CODEWITHSUNDEEP
sqlsql-serversql-server-2005
Allain Lalonde
Allain Lalonde

Reputation: 93348

Counting rows for all tables at once

I'm using SQL Server 2005 and would like to know how I can get a list of all tables with the number of records in each.

I know I can get a list of tables using the sys.tables view, but I'm unable to find the count.

Thank you

Upvotes: 16

Views: 35913

Answers (6)

Deep Kalra
Deep Kalra

Reputation: 1428

These all ways will give you the row count in the result set of a select query. SSMS also gives you an option to see the object details via: "Object Explorer Details" pane. This is available for 2005 and all versions after that

Object Explorer Details is a component of SQL Server Management Studio. It provides a tabular view of all the objects in the server and presents a user interface to manage them.

To reach "Object Details Window": Click Object you want to see details for in "Object Explorer" pane and press F7. Read More: https://technet.microsoft.com/en-us/library/ms191146(v=sql.90).aspx

Pros:

  1. Much faster then the select query as SQL Server stores this information
  2. Don't need to write/copy-paste the select query everytime - you are just one F7 away
  3. Gives you much more information and not just about row count

Note: You might not have Row Count coming in the Object Details pane by default, you can click on the column header and bring it in.

Upvotes: 1

joaopintocruz
joaopintocruz

Reputation: 291

This is my method:

create table #rowcount (tablename varchar(128), rowcnt int)
exec sp_MSforeachtable 
   'insert into #rowcount select ''?'', count(*) from ?'
select * from #rowcount
    order by tablename
drop table #rowcount

works like a charm

Upvotes: 1

Tom Wilson
Tom Wilson

Reputation: 551

For what it's worth, the sysindexes system table is deprecated in SQL 2008. The above still works, but here's query that works going forward with SQL 2008 system views.

select
schema_name(obj.schema_id) + '.' + obj.name,
row_count
from (
    select
        object_id,
        row_count = sum(row_count)
    from sys.dm_db_partition_stats
    where index_id < 2  -- heap or clustered index
    group by object_id
) Q
join sys.tables obj on obj.object_id = Q.object_id

Upvotes: 22

Kev
Kev

Reputation: 119806

I might add that sysindexes.rows is an approximation of the number of rows. I'd run a DBCC UPDATEUSAGE if you need a more accurate value. We had this issue on a DB with tables containing over 47-50 million rows and we thought we'd lost around half a million from each of them.

Upvotes: 5

brian
brian

Reputation: 3394

From here: http://web.archive.org/web/20080701045806/http://sqlserver2000.databases.aspfaq.com:80/how-do-i-get-a-list-of-sql-server-tables-and-their-row-counts.html

SELECT 
    [TableName] = so.name, 
    [RowCount] = MAX(si.rows) 
FROM 
    sysobjects so, 
    sysindexes si 
WHERE 
    so.xtype = 'U' 
    AND 
    si.id = OBJECT_ID(so.name) 
GROUP BY 
    so.name 
ORDER BY 
    2 DESC

Upvotes: 42

Paul Lefebvre
Paul Lefebvre

Reputation: 6406

Perhaps something like this:

SELECT 
    [TableName] = so.name, 
    [RowCount] = MAX(si.rows) 
FROM 
    sysobjects so, 
    sysindexes si 
WHERE 
    so.xtype = 'U' 
    AND 
    si.id = OBJECT_ID(so.name) 
GROUP BY 
    so.name 
ORDER BY 
    2 DESC

http://web.archive.org/web/20080701045806/http://sqlserver2000.databases.aspfaq.com:80/how-do-i-get-a-list-of-sql-server-tables-and-their-row-counts.html

Upvotes: 3

Related Questions