Run a script file against Multiple databases

Question

Run a script file against Multiple databases

Guras

SSCertifiable

Points: 6518
More actions
August 14, 2013 at 12:18 pm

#277033

I have a script file in C:\Script.sql that I need to run against multiple databases.
I can run on one database as follows
:r c:\Script.sql
But my challenge is I need to run on all the databases returned by the folowing query. The script file creates abount 50 objects .
SELECT db_name from Table1 where dbID > 4
Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Ed Wagner SSC Guru Points: 287026 More actions · Answer 1

If you use SSMS and use the Registered Servers tab to keep your connections organized, it does support this. Create a group (using New Server Group) and then add registrations to that group. Then, when you right-click the group name and click New Query, you'll open a query window connected to all servers under your group. The status bar will turn a different color (for me, it's pink) and tell you how many servers you're connected to. You can then run your query on all servers and get back your result sets with the server name.

I know, it's pretty well-hidden, but hey...we didn't write SSMS. 🙂

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Guras SSCertifiable Points: 6518 More actions · Answer 2

I forgot to mention that it is just on one server but there are multiple databases

Ed Wagner SSC Guru Points: 287026 More actions · Answer 3

That's my mistake. For some reason, I read your post and thought "multiple servers". Sorry about that.

To run a single script against multiple databases, you'll need to create a list of databases. Then iterate through each one and fire a USE command and then the command itself.

If this is a manual process you only want to run occasionally, here's a way to use dynamic SQL to generate the code for you to run against multiple databases. While I'm using sys.databases as the list in the example, you can substitute your list for it instead.

select 'use ' + name + '; SELECT db_name from Table1 where dbID > 4;'

from sys.databases;

You can then copy the code from the result set and run it. This does mean that you need a Table1 in each database.

The dbid > 4 and field name db_name (also a function) has me thinking about sys.databases.database_id. If all you're really after is a list of all database names, you can just select name from sys.databases and that will return it to you in a single query. I don't know if this is what you're really after or if you really need to select something from table1.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Bill Talada SSChampion Points: 11954 More actions · Answer 4

-- You can put this in your templates for easy reuse

declare @list table (name varchar(128) not null);

-- fill the list with your custom subset

insert into @list

select name from sys.databases where name like 'U%';

declare

@db varchar(128),

@t varchar(max),

@s-2 varchar(max)

;

-- do whatever you like

set @t = 'use {db}; exec sp_spaceused';

set @db = (select min(name) from @list);

while @db is not null

begin

set @s-2 = replace(@t, '{db}', @db);

exec (@s);

set @db = (select min(name) from @list where name > @db);

end

GO

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 5

Write a small program in C#/VBScript/Perl/whatever that runs the query, and then invokes SQLCMD once for each database. Or just simply run the script directly. (Running a script file is fairly simple. You need to parse out the go lines, but that is not really rocket science.)

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 6

One way to do it would be to take advantage of PowerShell remoting. You can pull from a list of databases (or generate them) and then run the commands you want to run. I have a blog post on it here[/url].

Another option is to look to a third party tool. Red Gate has one called SQL Multi-Script that does what you need.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

andreujuanc Valued Member Points: 55 More actions · Answer 7

Hello!, one month ago I checked this post, coming from google trying to find a solution to this problem.

Some answers are good, but i needed something that gives more feedback. So I made my own tool.

https://github.com/andreujuanc/TakoDeploy

Just in case you guys find it interesting.

Im still developing it, so bare with me 🙂