I found lots of methods for individually scripting out stored procedures, but I'd like to know if there's a way to do many (like 100 or so) at once.

Ideally, I'd like to execute something that would save all the procedures that start with 'dev_' to the file system somewhere, then delete the dev_ SP's from the database and leave the other SPs that actually get used.

Is that possible in one step?  Does any one have a link or an idea?

Thanks in advance!!

You have a couple options.  

Option 1: Use the scripting wizard Right-click the db --> tasks --> Generate scripts --> go through the wizard.

Option 2:  Open the stored procedures folder in SSMS (in the object explorer details window) You can use shift click to select all the stored procedures and you can then right_click and script them to a file.

Option 3: Create your own logic to script the stored procedures.  You can use TSQL or SMO for this.  This is by far the most time consuming of all the methods and it is kinda like reinventing the wheel. 

IMO the options 1&2 are easier and less time consuming, but are manual.  If you need to automate this process, you will need to create or download a solution.

Actually, I did slight modifications after that - here is a version that really scripts them:

set nocount on
DECLARE @Test TABLE (Id INT IDENTITY(1,1), Code varchar(max))

INSERT INTO @Test (Code)
SELECT 'IF object_ID(N''[' + schema_name(schema_id) + '].[' + Name + ']'') IS NOT NULL 
           DROP PROCEDURE ['+ schema_name(schema_id) +' ].[' + Name + ']' + char(13) + char(10) + 'GO' + 
           OBJECT_DEFINITION(OBJECT_ID) + char(13) +char(10) + 'GO' + char(13) + char(10)
            from sys.procedures
            where is_ms_shipped = 0

DECLARE @lnCurrent int, @lnMax int
DECLARE @LongName varchar(max)

SELECT @lnMax = MAX(Id) FROM @Test
SET @lnCurrent = 1
WHILE @lnCurrent <= @lnMax
      BEGIN
            SELECT @LongName = Code FROM @Test WHERE Id = @lnCurrent
            WHILE @LongName <> ''
               BEGIN
                   print LEFT(@LongName,8000)
                   SET @LongName = SUBSTRING(@LongName, 8001, LEN(@LongName))
               END
            SET @lnCurrent = @lnCurrent + 1
      END

These methods all work well!! 

I personally ended up using Naom's code, because I have been given the honor of performing this task on about 10 different databases.

Very, very helpful answers!!  Thank you!!!

Check the blog How to script all stored procedures in a database - I may enhance it later...

Why jump through all those hoops to script the sps out?  Just run a BCP command to print the sp code to a file automagically.

The command is this simple.

bcp "SELECT definition + char(13) + 'GO' FROM MyDatabase.sys.sql_modules s INNER JOIN MyDatabase.sys.procedures p ON [s].[object_id] = [p].[object_id] WHERE p.name LIKE 'Something%'" queryout "c:\SP_scripts.sql" -S MyInstance -T -t -w

I have to admit, this is very simple to use.  Thanks for another great answer!!

Perfect. The bcp utility works well. Thank you, Adam. You saved me 5 hours of scripting time!

• Edited by Ralph Counts Tuesday, August 26, 2014 6:34 PM

In my case I want to extract them to individual files for further version control work.  I would want this to happen nightly as an automatic capture of daily changes.

4 years later >_<