I have created the definition of more than 200 stored procedures. My problem is that I have to execute them on a different server and on a different database. The process which I am following is that first I check each stored procedure that it exists on that server and database or not. If not then I create them else I drop the earlier definition and create the new one.

So I just wanted to know that is there any other way out. As I have to perform this activity for each stored procedure. I want to create a script or so, which can run and create the definition of all my stored procedure on the server and database.

9 Answers 9


Try right clicking on the database in Management Studio and selecting Tasks > Generate Scripts. The wizard that runs will let you create scripts for any objects in the database. In your case you'll want to select all "Stored Procedures".

In SQL Server 2008, you can click the "Advanced" button and change the value of the "Script DROP and CREATE" property to "Script DROP and CREATE" if you want to generate both scripts.

In SQL Server 2005, you may need to generate one set of DROP scripts and then another set of CREATE scripts (sorry I don't have an instance handy to test with).

See this link for more info:


In your script that creates the stored procedures put the following:

IF OBJECT_ID('your_prodcedure') IS NOT NULL
    DROP PROCEDURE your_procedure
  • This I have done for all the stored procedure definition. I want to know how to automate the process the creating them on different server and database.
    – Rahul Tripathi
    Oct 7, 2012 at 15:32
  • you can use the 'USE' statement or just make sure your in that database when you run your scripts. I might not get exactly what you want. You could write a job that executes all your creation scripts
    – Grixxly
    Oct 7, 2012 at 15:33
  • Thanx Grixxly..but you got it right I want to know what should I right in that jobfor executing those creation scripts
    – Rahul Tripathi
    Oct 7, 2012 at 15:35

I would suggest to your attention the key part of MS SQL Server - Integration Services. This is a framework which should exactly fit to your needs. We are using it for ETL processes (load data from many systems into many DB; clean, validate, adjust, transform - process them; export results...).

SSIS (SQL Server Integration Services) is a powerful framework designed for standardized, repeatable operations. You can:

  • create/include your scripts there,
  • make them parametrized,
  • and finally schedule their execution in SQL Agent job.

Our experience is really good. It takes some time to learn it, but benefits should come very soon.

Try to check MSDN, my favourite blog (with brilliant naming conventions): is here


If you want a method to run 200 CREATE PROCEDURE statements, along with the checks to see if they already exist your options (which some have already been mentioned):

  • SSIS package
  • SQLCMD script
  • PowerShell Script using SQL 2005 SMO
  • Manually on each instance, as you are already doing I guess

Each option has their own level of complexity and knowledge to setup and run. If this is something you have to do frequently for moving code through environments you might find the time to learn and set one of the above options up worth your time.


You can combine all of this into a single script run it on all instances of DB

  • Thanx MichaelT. But I want to automate the process of creating the definition which I have created. Presently I am running the SQL server and on that I am running the stored procedure definition manually, so I just wanted to automate the process of manually running each stored procedure definition.
    – Rahul Tripathi
    Oct 7, 2012 at 15:33

Using a cursor you can get a list of databases and execute a script in a loop.

Try this:

 SELECT name FROM master..sysdatabases
 WHERE name NOT IN ('master','tempdb','model','msdb','distribution')
 AND status & 512 = 0


 SET @SQL = '---my script'

  1. Get all your objects in Source Control.
  2. Use a tool like Liquibase to deploy them. All effort to configure it are well worth it in the mid to long term. http://www.liquibase.org/
  1. If you have budget I would suggest to check out "SQL Multi Script" Product from RedGate.

  2. If you could make use of SQL Sever 2008 then check out "Central management Servers".

  3. Try out "Registered Server tools" from SSMS (I have used it in SQL Server 2008 but I think its available since SQL Server 2005 itself).

  4. Make use of Powershell scripts to loop through all your SQL Instance and execute the Stored Procs.


I believe the easiest option is using degining batch file.You can wrap all your scripts in one batch file and call this batch file from a SQL server job. To do that please have a look at the following post: How do I run a batch file from a scheduled Job via SQL Server Agent

Then you can schedule the SQL server Job as you require.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.