Row Count for each tables for entire Server

Question

Post reply

Row Count for each tables for entire Server

jack_hc

Old Hand

Points: 344
More actions
July 29, 2016 at 1:12 am

#400487

I know there're many posts around giving scripts (many of them are good) that can pull a report of all tables & their row count in a DB.
but i need one that can report all DBs on a server with rowcount info for each of tables.
what i can think of is sp_msforeachdb, wanted to modify current working script run for all DBs, but unable to make it working.
i have servers that running more than 100 DBs, each db with many tables, especially for SharePoint DB servers.
so, how i can get report for all Tables of ALL DBs on a Server?
Thanks,
Jack

Viewing 4 posts - 1 through 3 (of 3 total)

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

Umer Akram SSC-Insane Points: 22209 More actions · Answer 1

is this one is one time report or you want to store it one historic basis and want to compare data on different dates?

joeroshan SSChampion Points: 10377 More actions · Answer 2

What issues you got with msforeachdb?

One quick and dirty solution, please make your own changes

IF object_id('tempdb..##AllDBTablesRowcnt') is not null

Drop table ##AllDBTablesRowcnt

Select db_name() DBName,Object_name(object_id) TableName,rows into ##AllDBTablesRowcnt from sys.partitions where index_id in (0,1)

and 1=0

EXEC sp_MSforeachdb '

USE ?;

IF db_ID()>4

BEGIN

Insert into ##AllDBTablesRowcnt

Select db_name() DBName,Object_name(object_id) TableName,rows from sys.partitions where index_id in (0,1) ;

END

'

Select * from ##AllDBTablesRowcnt

----------------------------------------------------------------------------------------------------------------------------------------------------
Roshan Joe

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

jack_hc Old Hand Points: 344 More actions · Answer 3

Hi Roshan,

This works perfectly for me. thanks for the help.

i'm trying to make it as report and send to me by email daily in xls format. pls let me know if it is not too trouble for you.

My test run returned 10,860 rows in 51 seconds, wow, my huge server.

Thanks,

Jack

joeroshan (7/29/2016)
What issues you got with msforeachdb?
One quick and dirty solution, please make your own changes
IF object_id('tempdb..##AllDBTablesRowcnt') is not null
Drop table ##AllDBTablesRowcnt
Select db_name() DBName,Object_name(object_id) TableName,rows into ##AllDBTablesRowcnt from sys.partitions where index_id in (0,1)
and 1=0
EXEC sp_MSforeachdb '
USE ?;
IF db_ID()>4
BEGIN
Insert into ##AllDBTablesRowcnt
Select db_name() DBName,Object_name(object_id) TableName,rows from sys.partitions where index_id in (0,1) ;
END
'
Select * from ##AllDBTablesRowcnt