December 27, 2013 at 10:04 am
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
December 27, 2013 at 10:30 am
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
December 27, 2013 at 11:00 am
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!!!!!!!
December 27, 2013 at 11:04 am
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
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy