Friday, August 30, 2013

How to create a stored procedure on multiple databases

I noticed this particular question asked on one of the SQL forums the other day, however, this same question can apply to any database objects, like how to create a function on multiple databases, how to create a view on multiple databases, how to create a table on multiple databases, etc. If you think about it just for a bit you will realize that in fact the generic question that covers all those particular cases and more is: how to execute a t-sql script against multiple databases.
Well the best, safest and most efficient way for executing or deploying t-sql scripts on multiple databases is to utilize xSQL Software's Script Executor tool. Here is the breakdown of the time you would need to spend to accomplish your task:
  • Download and install Script Executor -> 2 minutes max
  • Create a database group and add your servers and databases into the group -> 30 seconds per server
  • Create one or more script containers and add your t-sql scripts to those containers -> less than 2 minutes depending on where the scripts are and how you might need to organize them
  • Map scripts to databases and set execution priorities -> 2 to 3 minutes depending on how complicated your deployment scenario is.
  • (optional) Create a deployment package -> 10 seconds (you would create a deployment package if you wish to executed the scripts from a machine where you might not have the Script Executor tool installed)
  • (optional) Create a batch file that executes the deployment package created above OR that executes a saved deployment project from the command line -> 2 minutes
  • (optional) Create a scheduled task that executes the batch file -> 1 minute
So, in about 10 minutes you will have created an automated job that deploys the scripts you want to the servers and databases you want, when you want! Now whenever you might wish to deploy one or more scripts that you have created to those databases all you need to do is drop the scripts to the folder(s) to which the Script Containers created above are pointing to and you are done - your scheduled task will take care of the rest.
Download Script Executor now and see what you have been missing.


Post a Comment