August 14, 2013 at 12:18 pm
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
August 14, 2013 at 12:27 pm
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. 🙂
August 14, 2013 at 12:31 pm
I forgot to mention that it is just on one server but there are multiple databases
August 14, 2013 at 12:40 pm
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.
August 14, 2013 at 1:07 pm
-- 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
August 14, 2013 at 3:38 pm
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]
August 15, 2013 at 6:02 am
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
October 11, 2016 at 6:41 pm
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 🙂
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply