How to count records in all the tables in database 3

[brettz]

Is there any command that will display a list of table names and the number of records associated with each table in the database?

Thanks in advance,

-Brett

 

[BobWman]

This should work

Select
Name = Substring(O.Name, 1, 30),
Rows = Convert(Char(12), I.Rows)
From
SysObjects O,
SysIndexes I,
Master.dbo.spt_values D
Where
I.ID = O.ID And
I.Indid < 2 And
(O.Type = 'U' Or O.Name = 'syslogs') And
D.Number = 1 And
D.Type = 'E'
Order By
Name

 

[brettz]

Thanks! It worked like a charm. Do you have any suggestions on where I can look for information on system queries like this one?

Thanks again,

-Brett

 

Sorry, I don't. I just happened to pick this one up from another developer.

 

[osjohnm]

Hi Brettz

If you are looking for queries or stored procedures that make use of the system tables quite often you can download scripts from the following sites:

http://www.swynk.com

http://www.sqlservercentral.com

I think the sites have links to other SQL sites as well

John

 

[jester777]

I know you have already got a solution to your original question but if you need to do things like this again you can use the stored procedure sp_MSforeachtable
eg
sp_MSforeachtable 'select count(*) &quot;?&quot; from ?'

this will do the same as the code you used previously.

There is also one called sp_MSforeachdb to allow you to perform commands against all databases.

These two stored procedures are not particularly well documented unfortunately.

 

[brettz]

Thanks jester777 777(?)

This was very helpful.

-Brett