Shortcuts? Tools? Running the same SQL on many dbs...
-
Folks,
We all have times when we need to run the same SQL repeatedly on multiple dbs. For instance, when you need to add a new UDF or SP on all your clients' databases, or when you need to change a field definition across all your Accounts tables, that sort of thing...
What is your favorite method? Do you...
a - Write the SQL, then write some wrapper SQL to loop through all the dbs returned by "select name from master.dbo.sysdatabases" ?
b - Use some home-grown tool?
c - Use some third-party tool?
d - ...something else?
We have to do this all the time, and we've long used (a) above. But its murder. Trying to parse all the quoted strings so that they work inside the wrapper is an agonizing exercise, introduces waves of new bugs, and makes error trapping very hard.
I've lately been working on (b) and am happy with it so far (it might become the preferred method among my clients). But I don't want to rebuild the wheel if someone's done this already.
How about you? How do you do this?
- Tom
-
Hello!
Just for pointing out: There is an undocumented SP by MS that is called sp_MSforeachdb. It is very similar to a) but if you want, read more at:
http://www.mssqlcity.com/FAQ/Devel/sp_msforeachdb.htm
Regards, Hans!
-
For me, for each of my procs, I create a txt file containing sql to drop the proc (if it exists), report the drop, create the proc and report the create.
I create the proc in dev, test it, and when OK, I use a DOS bat file to run isql to create the proc in the servers/databases required.
e.g. Create a bat file called r.bat with the following
isql /S servername /U username /P password -d datebase1 -b /m-1 /i%1 %2 %3
if ERRORLEVEL 1 pause
isql /S servername /U username /P password -d datebase2 -b /m-1 /i%1 %2 %3
if ERRORLEVEL 1 pause
isql /S servername /U username /P password -d datebase3 -b /m-1 /i%1 %2 %3
if ERRORLEVEL 1 pause
Then use the statement
r filename
which will issue the file for each server/database in the bat file.
The file can contain any sql.
Not much good if your databases change often but I find it gives me complete control.
Far away is close at hand in the images of elsewhere.
Anon. -
I use David's technique, but pipe the output to a single file. I then search for any error's that I've "echo"'d of there was an error.
-
hanslindgren,
Thanks, yes, I remembered that there was such a stored procedure...thanks for reminding me of its name. It might prove useful sometimes. We generally need to run our scripts on some subset of the databases, though, rather than all. And that stored procedure doesn't really "change db context" (or whatever the right phrasing would be) as it cycles through the dbs.
For instance, all our dbs have some form of a "Users" table. If I start up Query Analyzer, set the current DB to my "Client_A" database (lets say), and run this...
declare @sql as varchar(200)
set @sql = '
declare @intCount as int
select @intCount = count(*)
from users
print db_name() + '' '' + cast(@intCount as varchar(5))
'
exec sp_MSforeachtable @sql
...you would think I'd get a list of all my dbs and the count of records in each Users table. Rather, I get about 80 rows all telling me that my Client_A database has 5124 users. The "print" command just tells me over and over that db_name() = "Client_A".
Plus, this method would still require me to do a lot of ugly and hard-to-debug doubling of quote marks, and the like. (See the print command, and it's " + '' '' + " section.) (See, dang it, even that's hard to read!
- Tom
-
Tom,
You are using table instead of db
Try this
declare @sql as varchar(300)
set @sql = '
declare @intCount as int
if exists (select name from ?.dbo.sysobjects where name = '+CHAR(39)+'users'+CHAR(39)+' and type = '+CHAR(39)+'U'+CHAR(39)+')
begin
select @intCount = count(*)
from ?.dbo.users
print '+CHAR(39)+'?'+CHAR(39)+' + CHAR(32) + cast(@intCount as varchar(5))
end
'
exec sp_MSforeachdb @sql
Far away is close at hand in the images of elsewhere.
Anon. -
D'oh! Right, of course! Thanks...
Yeah, that works much better!
- Tom
-
I once found some VB6 code on the web somewhere that ran T-SQL batches across selected databases on all available servers. It got left behind a job or two ago, but it was pretty simple.
Start a VB project with SQL-DMO
Create a form with a TreeView, two RichTextBox, and two buttons (Execute and Exit)
Use SQL-DMO methods to enumerate all servers and all databases.
Populate the TreeView list with checkboxes for every database.
On Execute:
Clear the results RichTextBox
Scan the TreeView for all checked databases.
Connect a database object to each selected server & database in turn and use the ExecuteWithResults method to submit the text from the command RichTextBox as a T-SQL batch.
Append the results to the results RichTextBox (With heading for Server & Database)
If you take the trouble to find the original or write it yourself, you'll find it beats the heck out of sp_MSforeachdb. It's great for making sure the same change is applied to multiple servers (development & testing, for example).
-
Scott,
Thanks, great. Actually, that somewhat confirms my own analysis of the situation...and reasonably well describes the "home-grown tool" that I've built and am using. It's VB, with the SQL-DMO objects, and provides the list of databases, etc.
I'm actually in discussion with Brian Knight of SqlServerCentral.com to include it in either the freeware or shareware downloadables section of this site. At the risk of sounding like I've only posted this thread to generate interest, well...would any of you be interested in such a tool?
- Tom
-
I would be interested in the source code as freeware. I'd want source code because there's usually some reason to tinker with a tool like that to customize it to your environment, but it's a simple enough program that I probably wouldn't pay for it.
On the other hand, it's possible you might think of some amazing features that would tempt me to cough up a few bucks.
-
The main features I'm adding are the ability to save common sql statements that you would use to generate your list of dbs. For instance:
"All Client DBs" = select name from master.dbo.sysdatabases where name like 'client_%'
or
"All non-system DBs" = select name from master.dbo.sysdatabases where name not in ('master', 'msdb', etc...)
or, for my client
"All Active Clients" = select DbName from AdminDb.dbo.ClientDbList where bActive = 1 and bPaidUp = 0
That sort of thing. Then when you've selected from your list of available SQL Server instances on your network, you can pick one of your favorites from a dropdown, and it runs the associated query, and you have that list of DBs to work from. It can present that list either pre-checked or unchecked, as you like.
Other features pending, but that's where I'm going.
- Tom
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply