Help with getting a Table Count across multiple DBs

Question

Post reply

Help with getting a Table Count across multiple DBs

don 57678

SSC Enthusiast

Points: 152
More actions
December 27, 2013 at 10:04 am

#301653

Hello,
I'm not all that familiar with SQL scripting, I can usually figure stuff out, however I'm stuck on a project at work and have been searching for an answer with no luck. It may not ever be possible.
We have a SQL Server 2008 with approx 500 client DB's on them There is a specific Table called "CurrentUsers". What I'm trying to achieve is, I need to run this query on that table (Select Count (*) from CurrentUsers), which tells me how many users our on the application. The part here is I need to run this against ALL DB's (not including the system dbs), and have it give me a Sum of ALL CurrentUsers from all the DB's.
Is this even possible?
Thanks a bunch!!!
Don

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

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

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

Here's a code to show you how can it be done. You might need to fix it.

DECLARE @dbname sysname

CREATE TABLE #counts( totalcounts int)

DECLARE DBs CURSOR FOR

SELECT name

FROM sys.databases

WHERE database_id > 4

AND state = 0

OPEN DBs

FETCH NEXT FROM DBs INTO @dbname

WHILE @@FETCH_STATUS = 0

BEGIN

EXECUTE( 'INSERT INTO #counts SELECT COUNT(*) FROM [' + @dbname + '].sys.tables' )

FETCH NEXT FROM DBs INTO @dbname

END

CLOSE DBs

DEALLOCATE DBs

SELECT SUM(totalcounts)

FROM #counts

DROP TABLE #counts

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

don 57678 SSC Enthusiast Points: 152 More actions · Answer 2

I don't know how you guys know this stuff off the top of you head like this... Worked like a charm!!!! You just saved me a ton of time! Thank you Thank you Thanks you!!!!!!!

Lowell SSC Guru Points: 323504 More actions · Answer 3

my version is just an adaptation of Luis's: i'm just selecting the rows form the indexes, as the count is already materialized, and is faster on huge tables.

Thank you Luis for 99% of the code!

DECLARE @cmd VARCHAR(MAX),

@dbname VARCHAR(128)

CREATE TABLE #counts( DatabaseName VARCHAR(128),totalcounts INT)

DECLARE DBs CURSOR FOR

SELECT name

FROM sys.databases

WHERE database_id > 4

AND STATE = 0

OPEN DBs

FETCH NEXT FROM DBs INTO @dbname

WHILE @@FETCH_STATUS = 0

BEGIN

SET @cmd = '

INSERT INTO #counts(DatabaseName,totalcounts)

SELECT

''' + @dbname + ''' AS DatabaseName,

SUM(ROWS) AS NumRows

FROM ' + QUOTENAME(@dbname) + '.sys.partitions p

INNER JOIN ' + QUOTENAME(@dbname) + '.sys.sysobjects o

ON p.object_id = o.id

WHERE p.index_id IN (0,1)

AND o.type = ''U''

AND o.name = ''CurrentUsers''

GROUP BY p.OBJECT_ID,p.index_id '

PRINT @cmd

EXEC(@cmd)

FETCH NEXT FROM DBs INTO @dbname

END

CLOSE DBs

DEALLOCATE DBs

SELECT * FROM #counts

SELECT SUM(totalcounts) FROM #counts

drop table #counts

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!