t-sql stored procedure create scripts

Question

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