• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 882
  • Last Modified:

Maintenance plan to run a script for multiple databases

Hi All, I need to run a script on many servers (Exec USP_XYZ) which will be doing a bunch of delete and copy commands in several tables.

The wizard which has the option to select multiple databases DOES NOT have the option to run scripts. Only backup, shrink, etc...

I do not wish to create a job for every database, is it possible to create a plan that will be running for each database without using the wizard? or even using the wizard, does anyone know how to run a "EXEC usp_proc" on mulitple databases?

TIA
0
sir-rob
Asked:
sir-rob
  • 4
  • 3
1 Solution
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Kindly give inputs on the below questions:

1. Can you kindly confirm whether it is on Multiple databases in a single server or databases in multiple servers.
2. what is your SQL Server version and edition.

Without the above inputs, anyhow you can perform it via SQLCMD if it is in databases across multiple servers as given below:

http://www.sqlservercentral.com/articles/SQLCMD/66183/
0
 
sir-robAuthor Commented:
rrjegan17:

1. Can you kindly confirm whether it is on Multiple databases in a single server or databases in multiple servers.
One server, but lots of databases in the server... I don't want to create one job per database.

2. what is your SQL Server version and edition.
2005 - And all I want is how to create this plan so I can run a "EXEC usp_abc" proc...

Thanks!

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> One server, but lots of databases in the server.

Ok, in that case use Execute T-SQL Task and provide your Procedure in the way it is:

USE ur_db1
GO
EXEC usp_abc

USE ur_db2
GO
EXEC usp_abc

and so on..
0
 
sir-robAuthor Commented:
I did try USE... and it works but again, there are way too many databases....

What do you think about a Cursor?

Thanks!
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> it works but again, there are way too many databases....

Do you want to run it on all databases in your server, then you can use

EXEC sp_MSforeachdb 'USE ? ; exec usp_abc'

If you want to exclude few databases like system databases, then you can use the code below:

EXEC sp_MSforeachdb 'USE ? ; if ( DB_NAME() NOT IN(''master'', ''model'', ''msdb'', ''tempdb'')) begin exec usp_abc end'
0
 
sir-robAuthor Commented:
rrjegan17: what if there is a "-" in the name (dash), is there a way around it?

Btw, I am giving you the points, just wanted to see if you know of a solution (since it fails and will not finish)...

Thanks
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Having - in a database is not a recommended coding practise..
Anyhow, the below code would work, you can see there is a db named test_db-1 in the list for which I tested

EXEC sp_MSforeachdb 'USE [?] ; if ( DB_NAME() NOT IN(''master'', ''model'', ''msdb'', ''tempdb'', ''test_db-1'')) begin exec sp_helpdb end'
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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