Solved

How to change database context dynamically

Posted on 2014-10-06
4
312 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 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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
Comment Utility
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
Comment Utility
>> 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
Comment Utility
Thanks guys. I ended up running it through sqlcmd and building the db statement dynamically and executing via xp_cmdshell.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

772 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

11 Experts available now in Live!

Get 1:1 Help Now