Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

migrated from stackoverflow.com Oct 7 '12 at 23:00

This question came from our site for professional and enthusiast programmers.

9 Answers

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:
http://blog.sqlauthority.com/2007/08/21/sql-server-2005-create-script-to-copy-database-schema-and-all-the-objects-stored-procedure-functions-triggers-tables-views-constraints-and-all-other-database-objects/

share|improve this answer
add comment

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.

share|improve this answer
add comment

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

share|improve this answer
    
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 '12 at 15:33
add comment

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

IF OBJECT_ID('your_prodcedure') IS NOT NULL
    DROP PROCEDURE your_procedure
GO
share|improve this answer
    
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 '12 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 '12 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 '12 at 15:35
add comment

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

Try this:

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

DECLARE @DB VARCHAR(100)
 DECLARE @SQL VARCHAR(8000)

OPEN DBList
 FETCH NEXT FROM DBList INTO @DB WHILE @@FETCH_STATUS <> -1
 BEGIN
 SET @SQL = '---my script'
 EXEC(@SQL)
 FETCH NEXT FROM DBList INTO @DB
 END

DEALLOCATE DBList
share|improve this answer
    
Careful, this has the same problem as sp_MSforeachdb in that it can skip databases on a busy system. See [sqlblog.com/blogs/aaron_bertrand/archive/2010/12/29/… and [mssqltips.com/tip.asp?tip=2201](here). At the very least use LOCAL FAST_FORWARD in your cursor declaration. –  Aaron Bertrand Oct 8 '12 at 13:50
add comment
  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/
share|improve this answer
add comment

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

share|improve this answer
add comment
  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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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