Solved

How to script all sp's in a database

Posted on 2008-10-13
3
205 Views
Last Modified: 2012-05-05
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
Comment
Question by:craigdev
3 Comments
 
LVL 23

Accepted Solution

by:
adathelad earned 400 total points
ID: 22701458
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
 
LVL 60

Assisted Solution

by:chapmandew
chapmandew earned 50 total points
ID: 22701476
another way to do it in 2005:

select object_definition(object_id) from sys.procedures
0
 
LVL 9

Assisted Solution

by:Sander Stad
Sander Stad earned 50 total points
ID: 22701488
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now