t-sql stored procedure create scripts

Question

Hi

I have a bunch of stored procedure names. I want to export the create script for each of the stored procedure. What is the best way to do it?

Right now I am manually selecting the stored proc in SSMS and selecting "Script stored procedure as -> Drop and Create to". This seems tedious. I am hoping there is a better way to deal with this. Thanks.

Answer 1

You can right-click on the database in the Object Explorer and do a Task > Generate Scripts.

That allows you to pick a whole bunch of objects to be scripted (e.g. tables, views, stored procs) and you can store those into a single big SQL file, or one SQL file per object. Works really quite well!

Update: if you want to do this in the SQL Server Management Studio app, you can use this SQL script to find the stored procs and their definitions - you cannot however have SQL Server Mgmt Studio write out the files to disk, that doesn't work - but you can copy the results into e.g. Excel.

SELECT 
    pr.name ,
    pr.type_desc ,
    pr.create_date ,
    mod.definition
FROM sys.procedures pr
INNER JOIN sys.sql_modules mod ON pr.object_id = mod.object_id
WHERE pr.Is_MS_Shipped = 0

Answer 2

If you select View --> Summary

Then Click "Stored procedures" from the object explorer it will provide you with a list of all your stored procedures which you can Ctrl/Shift select (select multiples). Then from there you can create all the drop scripts at once then all the create scripts. This is one of the many quirks I've found with SSMS.

Note: Another neat feature is the filter option, allowing you to filter through your stored/tables procedures with ease. Simply right click in the object explorer to bring up the menu.

Answer 3

To script out all ones matching a particular criteria you could use something like the below. (though probably it should look at the values in the uses_ansi_nulls, uses_quoted_identifier columns for correctness)

DECLARE @t varchar(max)
set @t = ''

select @t = @t + definition + ' 

GO

' from [sys].[sql_modules]
WHERE  OBJECTPROPERTY (object_id,'IsProcedure' )=1
AND  Object_name(object_id) LIKE '%SomePattern%'

/*Stops the long text from getting truncated in SSMS*/
SELECT @t AS [processing-instruction(x)] FOR XML PATH('')

Answer 4

You might look at sp_helptext for some ideas about how you can leverage that to create your scripts.

Answer 5

Visual Studio 2008 Database Professional Edition and Visual Studio 2010 Professional (and above) supports special project types for SQL Server 2005/2008. These projects support the automatic creation of change scripts, containing all changes between the current project and a specified target database.

AFAIK RedGate also provides some tools like this, though, I don't have any experiences with them.