Need to find the number of rows in every table in a DB

Question

Post reply

Need to find the number of rows in every table in a DB

kwitzell

SSCrazy

Points: 2470
More actions
May 18, 2006 at 9:33 am

#113447

Is there a SQL command that will let me find the number rows of every table in a SQL server database? In other words a command or sp that when run would output the number of rows in every table in one go. I have a SQL database that has gotten rather large and would like to figure out which table is using all of the space without having to go through Enterprise Manager or QA and look at them individually.
Thanks!
Ken

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

David Burrows SSC Guru Points: 65142 More actions · Answer 1

You can use an undocumented procedure

sp_msforeachtable 'SELECT ''?'',COUNT(*) FROM ?'

also you could create a temp table and insert the results to it

Far away is close at hand in the images of elsewhere.
Anon.

R2ro Default port Points: 1439 More actions · Answer 2

Another (documented) option & rather fast...

DBCC

SHOWCONTIG WITH TABLERESULTS

John Rowan SSC Guru Points: 56440 More actions · Answer 3

Run this through QA, output to File. Open the file in QA and run. Or, output to text and cut/paste into new QA window and run.

set nocount on

SELECT 'CREATE TABLE #tmpTableCount (TableName varchar(50),TableCount int)'

SELECT 'INSERT INTO #tmpTableCount (TableName, TableCount) SELECT ''' + [Name] + ''' ,COUNT(*) FROM ' + [Name]

FROM SYSOBJECTS

WHERE xtype = 'U'

SELECT 'SELECT TableName, TableCount FROM #tmpTableCount'

SELECT 'DROP TABLE #tmpTableCount'

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

kwitzell SSCrazy Points: 2470 More actions · Answer 4

kwitzell

SSCrazy

Points: 2470

May 18, 2006 at 10:22 am

#638795

Thanks! those did the trick

Ken

Gabor Nyul SSCrazy Eights Points: 9459 More actions · Answer 5

If an approximatve value is enough for you then this would give you what you want:

selectO.name,

rowcnt

fromsysindexes I join sysobjects O on O.id = I.id

whereindid < 2

andO.type = 'U'

order

byO.name

You can improve the exactness by running dbcc updateusage (0) with count_rows

Bye
Gabor