CODEWITHSUNDEEP
sqlwhile-loop
Frontier3
Frontier3

Reputation: 1

Creating a while loop to run a select statement against a list of DBs within the same server

We have servers with 100 DBs each. I want to run a select statement on approximately 50-75 of the databases on each server.

I can write a select statement to put the necessary DBs into a temp table.

From there I want to run a while loop that loops through each DB and runs the select statement, so that I can figure out which DBs have this active account. It needs to be run regularly which is why I am not just doing it manually.

Here is what I have. It gives me an error on my 'USE' command, saying incorrect syntax near @hospuse

DECLARE @hospcounter INT; 
DECLARE @hospuse varchar(100);
DECLARE @userlogin varchar(50);
SET @hospcounter = 0;
SET @hospuse = (select name from #tempdbnames where hospnumber = '1')
SET @userlogin = 'dmarch'

SELECT IDENTITY(int, 1,1) AS hospnumber, name into #tempdbnames
FROM master.dbo.sysdatabases
where name NOT LIKE ('%storage_0%')
AND NAME NOT LIKE ('%WSR%')
AND name NOT LIKE ('Z%')
AND name NOT LIKE ('master')
AND name NOT LIKE ('model')
AND name NOT LIKE ('msdb')
AND name NOT LIKE ('tempdb')
AND name NOT LIKE ('_Placeholder')




WHILE @hospcounter <= (select MAX(hospnumber) from #tempdbnames)
BEGIN
USE @hospuse
SELECT ro.user_login, ro.activated from rev_operator ro where   ro.user_login = @userlogin and ro.activated != '0'

SET @hospcounter = @hospcounter + 1;
SET @hospuse = (select name from #tempdbnames where hospnumber = @hospcounter);
END;

PRINT 'Done';
GO

drop table #tempdbnames

I also tried this with sp_msforeachdb but couldn't figure out how to get the syntax right for filtering the DBs I wanted to use without literally saying NOT IN and listing every DB I didn't want. Which doesn't work because DBs get added regularly.

Upvotes: 0

Views: 141

Answers (1)

sqlnick
sqlnick

Reputation: 11

When trying to run the same query across multiple databases, cursors actually will be a good option. 

DECLARE @Databases Table (DBName varchar(256))
DECLARE @Name varchar(256)
DECLARE @SQL Nvarchar(max) 
DECLARE @userlogin varchar(50)
SET @userlogin = 'dmarch'

INSERT @Databases
select name from sys.databases where  name NOT LIKE ('%storage_0%')
AND NAME NOT LIKE ('%WSR%')
AND name NOT LIKE ('Z%')
AND name NOT LIKE ('master')
AND name NOT LIKE ('model')
AND name NOT LIKE ('msdb')
AND name NOT LIKE ('tempdb')
AND name NOT LIKE ('_Placeholder')




DECLARE DBCursor Cursor For
Select DBName
from @Databases

OPEN DBCursor
FETCH NEXT
FROM DBCursor
INTO @Name

WHILE @@FETCH_STATUS = 0  
BEGIN

SET @SQL = N'USE ['+@Name+'] 
select ''['+@Name+']'',  ro.user_login, ro.activated from rev_operator ro where ro.user_login = @userlogin and ro.activated != ''0'''

EXEC sp_executesql @SQL


FETCH NEXT
      FROM DBCursor
      INTO @Name
END

CLOSE DBCursor
DEALLOCATE DBCursor

Upvotes: 1

Related Questions