Query row count across several databases

Question

Query row count across several databases

Adrian Smith-294236

Old Hand

Points: 391
More actions
October 7, 2010 at 1:13 am

#97341

Hi All,
I have a server which hosts > 50 databases. About half of these have an identical structure and contain a table which houses the contents of emails which are sent out at scheduled intervals. This process is performed by an application which occasionally fails.
I would like to write a script which queries the mail table of the relevant databases and records the date/time, row count and database name.
I have a very crude script which uses sp_foreachdb to return the row count but I cannot find a way to marry those results to the database name.
Thanks,
Adrian.

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

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

Adrian Smith-294236 Old Hand Points: 391 More actions · Answer 1

OK - I should have read the manual. getting the database name is easy.

Now I'd like to refine my very crude script to automatically skip a database if the mail table is not found. At present I am doing this with a series of IF statements:

EXECUTE sp_msforeachdb 'USE ?

IF DB_NAME() NOT LIKE(''WEB10%'')

AND DB_NAME() NOT IN(''WEB1'', ''WEB2'',''WEB3'',''WEB4'')

AND DB_NAME() LIKE (''WEB%'')

SELECT DB_NAME() AS "Database", GetDate() AS "Date Time", COUNT (*) FROM ImmMailToSend AS MailCount'

I am trying to exclude WEB10_x_x databases because the script generates a message:

Could not locate entry in sysdatabases for database 'WEB10'

Which is a pain but something I need to resolve. The subject of another thread I suspect.

A.

Nakul Vachhrajani SSChampion Points: 10294 More actions · Answer 2

I am assuming that the table name is ImmMailToSend. With this assumption, does the following work for you?

EXEC sp_msforeachdb 'USE ? IF EXISTS (SELECT * FROM sys.tables st WHERE st.name = ''ImmMailToSend'') SELECT DB_NAME() AS "Database", GetDate() AS "Date Time", COUNT (*) AS "MailCount" FROM ImmMailToSend'

Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com

Follow me on
Twitter: @sqltwins

Adrian Smith-294236 Old Hand Points: 391 More actions · Answer 3

Adrian Smith-294236

Old Hand

Points: 391

October 7, 2010 at 7:28 am

#1233543

Works like a charm! Thank you so much.

A.

Nakul Vachhrajani SSChampion Points: 10294 More actions · Answer 4

Not a problem at all. You are most welcome.

Have a wonderful rest-of-the-day ahead!

Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com

Follow me on
Twitter: @sqltwins

WayneS SSC Guru Points: 95460 More actions · Answer 5

Here's an alternative way that doesn't require verifying if the table exist, and doesn't perform a count(*) against the table if it does:

IF OBJECT_ID('tempdb..#TEST') IS NOT NULL DROP TABLE #TEST;

CREATE TABLE #TEST([Database] sysname, [Date Time] datetime, Quantity int);

execute sp_MSforeachdb 'INSERT INTO #TEST

SELECT DB_NAME(),

GETDATE(),

sp.[rows]

FROM [?].sys.partitions sp

JOIN [?].sys.tables st

ON st.object_id = sp.object_id

WHERE st.name = ''ImmMailToSend'';';

SELECT * FROM #TEST;

DROP TABLE #TEST;

(those are all single-quotes used in the code)

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Adrian Smith-294236 Old Hand Points: 391 More actions · Answer 6

Many thanks - I can see the sense in using a temporary table. Unfortunately I'm away for a few days but I'll try this out when I get back