Tables & the rowcount - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Tables & the rowcount

Author

Topic

8022421
Starting Member

45 Posts

Posted - 2010-05-10 : 05:41:26

HI,
I need a query to get the list of tables and the count of the rows in the table in a particular database, Can any one help me in this.

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-05-10 : 05:52:00

Use this -
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

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2010-05-10 : 06:08:26

select distinct
sys.tables.name,sys.partitions.rows
from sys.partitions inner join sys.tables
on sys.partitions.object_id = sys.tables.object_id
ORDER BY SYS.partitions.rows desc

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-05-10 : 06:34:10

Not distinct, that'll give some odd results if tables are partitioned. Also no need for distinct if you limit the index id or 0 or 1

SELECT OBJECT_NAME(p.object_id) AS TableName, SUM(p.rows) AS TotalRows
FROM sys.partitions p
WHERE index_id IN (0,1) -- just the cluster/heap. Nonclustered indexes would add dup rows
GROUP BY object_id

--
Gail Shaw
SQL Server MVP

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-05-10 : 09:10:04

Also refer this
http://beyondrelational.com/blogs/madhivanan/archive/2007/11/02/different-ways-to-count-rows-from-a-table.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources