Solved

How to change database context dynamically

Posted on 2014-10-06
4
331 Views
Last Modified: 2014-10-07
Hey folks,

I have a script I need to run against ~300 databases. using stored procedures (sp_msforeachdb, exec) aren't viable because of batch separators in the form of GO statements throughout the script.

What's the best way to dynamically context and then run the script verbatim? The script is 1200+ lines, so I'm trying to avoid having to edit the script and dynamically assign fully qualified server/db names throughout.

Let me know if I didn't provide enough information.

basically I'm looking to do something like:

for each (@db in dbs)
 use @db;
run rest of script
0
Comment
Question by:FroggedUp
  • 2
4 Comments
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 40365354
Like this:
DECLARE @MySQLStatement nvarchar(MAX);
DECLARE @MySQLScript nvarchar(MAX);

for each (@db in dbs) 
SET @MySQLScript = 'code of rest of script here'
SET @MySQLStatement = N'use ' + @db + '; EXEC sp_executesql N''' + @MySQLScript  + ''''
EXEC (@MySQLStatement )

Open in new window

0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 500 total points
ID: 40366458
Create the script as a proc in the master db, naming it "sp_<whatever>", and marking it as a "system" proc.

Then, when you invoke it, it will run in the context of the current db (which, as always, can be modified by specifying the db name on the EXEC).

EXEC db1.dbo.sp_yourproc
EXEC db2.dbo.sp_yourproc
EXEC db3.dbo.sp_yourproc
...
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 500 total points
ID: 40366548
>> using stored procedures (sp_msforeachdb, exec) aren't viable because of batch separators in the form of GO statements throughout the script. <<

If it's generated from SSMS, you can turn off the "GO"s being generated into the script (the option is probably not labeled what you'd expect it to be though: it's the very first option, "Delimit individual statements").

Other than a stored proc(s), you'd probably have to use sqlcmd or Powershell.
0
 

Author Comment

by:FroggedUp
ID: 40367014
Thanks guys. I ended up running it through sqlcmd and building the db statement dynamically and executing via xp_cmdshell.
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how the fundamental information of how to create a table.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now