[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 679
  • Last Modified:

Can I run one script in multiple sql databases at once ?

I have a server with over 50 databases, they are all exactly the same, for liability reasons we need to keep them separate. Every week or so I need to run a script to update them, it is the same script for all 50 databases.
Is there a way to run the script ONCE and have it update ALL selected databases ?  It is a pain to have to run it 50 times.
I am using MS SQL 2008 RC2.
0
Aleks
Asked:
Aleks
  • 2
  • 2
1 Solution
 
itmarsolutionsCommented:
yes you can do this.
use the below script to loop through database.

DECLARE @name VARCHAR(50) -- database name  
DECLARE db_cursor CURSOR FOR  
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')  
order by name

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @name  

WHILE @@FETCH_STATUS = 0  
BEGIN  
      print ' Starting ...' + @name
      --your code  goes here
      FETCH NEXT FROM db_cursor INTO @name  
END  

CLOSE db_cursor  
DEALLOCATE db_cursor
0
 
AleksAuthor Commented:
What if I want to select which databases to run it on ?
There are a few databases there that are not related to this one particular service and those need to remain untouched.
0
 
itmarsolutionsCommented:
you can avoid that database in this line

WHERE name NOT IN ('master','model','msdb','tempdb')
0
 
AleksAuthor Commented:
Thanks .. I actually found the perfect tool. RedGate has 'multi script', allows to run scripts in multiple databases and actually multiple sql servers as well. Will make managing the lists of databases easier !

Thanks though !

A
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now