Is there any way to run the definition of multiple stored procedure at a time?

Question

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.

Answer 1

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/

Answer 2

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.

Answer 3

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

Answer 4

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

IF OBJECT_ID('your_prodcedure') IS NOT NULL
    DROP PROCEDURE your_procedure
GO

Answer 5

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

Answer 6

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/

Answer 7

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

Answer 8

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.

Answer 9

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.