Technical Article

Exec scripts in a group of databases

,

If you generally execute your scripts against a group of databases, create a template like the one above and drag it to the top of your script in the script window. Hit execute three times to cycle through them to apply your script to all databases in the group.

Make the group circular if you want to be able to start with any database in the group.
Make it linear if you always want to execute changes to older or newer versions in a progressive manner.

The first three lines above make it jump into the group from any database. You can remove those lines for more secure control.

If you implement a circular list, start with the last database in your group so it will be changed to the first before executing your script, if order is important to you.

Sometimes it is little tricks like this that make the workday so much better. F.Y.I. a "USE" statement may not be put in a stored procedure, trigger, or function.

if db_name() not in ('master','model','msdb') 
use master
else 
if db_name() = 'master' 
use model 
else 
if db_name() = 'model' 
use msdb
else
if db_name() = 'msdb'
use master
go

-- scripts to execute in database group
select * from sys.tables

Rate

2.5 (2)

You rated this post out of 5. Change rating

Share

Share

Rate

2.5 (2)

You rated this post out of 5. Change rating