• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 217
  • Last Modified:

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.
0
craigdev
Asked:
craigdev
3 Solutions
 
adatheladCommented:
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
 
chapmandewCommented:
another way to do it in 2005:

select object_definition(object_id) from sys.procedures
0
 
Sander StadCommented:
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

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now