How to script all sp's in a database

Hi, is there an easy way to script all of the stored procedures in a database?

I'm refreshing the data in a dev environment and don't want to lose the new stored procs ... so need to create them with Create or Alter. Is there a sql server equivalent of 'create or alter' in Mysql?

Thks.
LVL 1
craigdevAsked:
Who is Participating?
 
adatheladConnect With a Mentor Commented:
Hi,

>> is there an easy way to script all of the stored procedures in a database?
yes. In SQL Server Management Studio, right click the database and select "Tasks -> Generate Scripts...". Follow the wizard through until the "Choose Object Types" dialog, and just tick the "Stored Procedures" box.

>> Is there a sql server equivalent of 'create or alter' in Mysql?
No. When deploying updates to sprocs that may or may not already exist, you'd usually drop and recreate them.

e.g.
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[YourStoredProcedureName]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    DROP PROCEDURE YourStoredProcedureName
GO

CREATE PROCEDURE YourStoredProcedureName
...
...
0
 
chapmandewConnect With a Mentor Commented:
another way to do it in 2005:

select object_definition(object_id) from sys.procedures
0
 
Sander StadConnect With a Mentor Systeemontwikkelaar, Database AdministratorCommented:
You can also use the added code. It reaturn all the stored procedures from a database.

WITH ROUTINES AS (
	SELECT 
		o.type_desc AS ROUTINE_TYPE ,
		o.[name] AS ROUTINE_NAME ,
		m.definition AS ROUTINE_DEFINITION 
	FROM sys.sql_modules AS m 
		INNER JOIN sys.objects AS o ON m.object_id = o.OBJECT_ID
)
 
SELECT * FROM ROUTINES

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.