September 3, 2009 at 8:35 am
I've run into this issue many times, but never found a solution. If it's obvious, I apologize, but I've also not found an answer in all my searches.
Basically, I would like to be able to cycle through a number of databases and then execute a series of commands against each database.
For example, the basic structure of what I want to do is:
DECLARE cursorDatabases CURSOR FOR
SELECT [Name] as DBName FROM sys.databases
WHERE [Name] LIKE 'nav%'
OPEN cursorDatabases
FETCH NEXT FROM cursorDatabases INTO @cDBName
WHILE (@@FETCH_STATUS <> -1)
BEGIN
USE @cDBName
--perform statements against the current database
FETCH NEXT FROM cursorDatabases INTO @cDBName
END
DEALLOCATE cursorDatabases
However, this fails on the USE statement and I have found no way to successfully change the current database context within a loop so that I can perform the desired tasks against each database that I want to work with.
Any ideas?
Thanks,
Steve
September 3, 2009 at 8:39 am
Dynamic SQL is the usual tool for this kind of thing. You create a varchar variable, populate it with the commands you want by concatenating the database name into it, and then execute it.
For example:
while @@fetch_status = 0
begin
set @Cmd = 'dbcc checkdb ' + @DBName;
exec (@Cmd);
fetch next from MyCursor
into @DBName
end;
Does that help?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 3, 2009 at 8:46 am
Thanks for the quick reply.
I've used that approach with the statement(s) that I wanted execute are limited, but that seems to fall short if I want to do anything more extensive. In this particular case, I'm looking to synch up permissions across all databases that our company has created at a customer site. So for each database that falls into that category (i.e. the LIKE 'nav%' clause used in creating the cursor), I'm looking to do the following:
- Check to see if a database role exists and if not, create it
- Check to see if the databaserole has permissions for all of the tables, views, and procs in that database
As you can see, all of the logic associated with those tasks won't fit easily (or at least cleanly) into a single @cmd value to be executed. At least I don't think so - maybe I'm giving up on that prematurely.
Is there no way to change the current db context within a cursor (or other loop construct)?
Steve
September 3, 2009 at 8:55 am
No, there's no way to do this. The hack would be to call a script that changed the context and executed something. However it would need to be some sort of shell out script since you can't have "use" in a stored proc.
This is really why Powershell was built, to handle things like this. T-SQL operates inside a database for the most part. It doesn't really handle cross database stuff pro grammatically very well.
September 3, 2009 at 9:45 am
Since you can use Exec with varchar(max), you're not really limited on this, but the string-build can get a little complex, especially if you have nested single-quotes.
Exiting the SQL context and using a command-line batch might be better. CLR might work too, since you can define the connection in the proc, and can do so dynamically.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 3, 2009 at 11:41 am
As Gus says, you just make a big @cmd string, as big as you need. Put the 'USE [' + @dbname + '];' at the beginning and that should do it.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 4, 2021 at 1:03 am
here is another simple modified usage from an earlier example which shows reindex and update stats to all databases minus the system ones. Works great:
declare @statement NVARCHAR(1000)
DECLARE @Database NVARCHAR(255) ;
declare @sql nvarchar(4000);
DECLARE DatabaseCursor CURSOR READ_ONLY FOR
SELECT name FROM master.sys.databases
WHERE name NOT IN ('master','msdb','tempdb','model','distribution') -- databases to exclude
--WHERE name IN ('profile') -- use this to select specific databases and comment out line above
AND state = 0 -- database is online
AND is_in_standby = 0 -- database is not read only for log shipping
ORDER BY 1
OPEN DatabaseCursor
FETCH NEXT FROM DatabaseCursor INTO @Database
WHILE @@FETCH_STATUS = 0
begin
set @sql = 'use ' + @Database + ' ; EXEC sp_MSforeachtable @command1="print ''?'' DBCC DBREINDEX (''?'', '' '', 80)"'
exec sp_executesql @sql;
set @sql = 'use ' + @Database + ' ; EXEC sp_updatestats'
exec sp_executesql @sql;
FETCH NEXT FROM DatabaseCursor INTO @Database
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply