List all the Stored Procedures of a Database and their Definitions using T-SQL in SQL Server 2005/2008

One way to Script the Stored Procedures of your SQL Server 2008 database is to use the SQL Server Management Studio (SSMS). Open SSMS > Your Database > Programmability > Stored Procedures > Right Click Stored Procedure Name > 'Script Stored Procedure As' > 'Create To'

The other way to script objects of your database in SQL Server 2005/2008 is to use the Microsoft SQL Server Database Publishing Wizard 1.1.

However if you need to do the same task programmatically using T-SQL, then here's the T-SQL that will help you do so:


USE NORTHWIND


 


SELECT obj.Name as SPName,


modu.definition as SPDefinition,


obj.create_date as SPCreationDate


FROM sys.sql_modules modu


INNER JOIN sys.objects obj


ON modu.object_id = obj.object_id


WHERE obj.type = 'P'




This query will list down all the stored procedures and their definitions of your database. In order to save the results of this SELECT statement to a text file, check this post of mine

Save SELECT query output to a text file
at

5 comments:

  1. AnonymousMarch 20, 2009 at 1:45 AM

    If You want to script an entire database for the purpose of source code control (e.g. Subversion), try out ScriptDB : http://www.codeplex.com/scriptdb .

    It generate scripts in a folder structure similar to the tree structure in Management Studio, and it can also be used from the command line.

    ReplyDelete
  2. Suprotim AgarwalMarch 20, 2009 at 9:24 PM

    Thanks Andreas for letting us know about ScriptDB!

    ReplyDelete
  3. UnknownFebruary 3, 2010 at 6:36 AM

    Another way to script procedures,triggers,etc to each file is

    http://beyondrelational.com/blogs/madhivanan/archive/2009/10/26/script-out-procedures-to-seperate-files.aspx

    Madhivanan

    ReplyDelete
  4. AnonymousFebruary 25, 2014 at 7:45 AM

    SELECT SPECIFIC_SCHEMA + '.' + [Routine_Name] AS ProcedureName, ROUTINE_DEFINITION
    FROM [INFORMATION_SCHEMA].[ROUTINES]
    WHERE [ROUTINE_TYPE] = 'PROCEDURE'

    ReplyDelete
  5. DavidJuly 31, 2015 at 1:39 AM

    Amazingly useful piece of code. I would suggest
    modu.definition + 'GO' as SPDefinition
    however, so that you can then copy+paste the results into SSMS and run them all (or make changes like I did).

    ReplyDelete