Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Help with getting a Table Count across multiple DBs Expand / Collapse
Author
Message
Posted Friday, December 27, 2013 10:04 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 3, 2014 7:39 AM
Points: 2, Visits: 9
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
Post #1526218
Posted Friday, December 27, 2013 10:30 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 12:17 PM
Points: 3,358, Visits: 7,263
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.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1526223
Posted Friday, December 27, 2013 10:59 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 3, 2014 7:39 AM
Points: 2, Visits: 9
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!!!!!!!
Post #1526227
Posted Friday, December 27, 2013 11:04 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:24 AM
Points: 12,887, Visits: 31,835
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1526230
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse