[sql-server] How to view the stored procedure code in SQL Server Management Studio

I am new to SQL Server. I am logged into my database through SQL Server Management Studio.

I have a list of stored procedures. How do I view the stored procedure code?

Right clicking on the stored procedure does not have any option like view contents of stored procedure.

Thanks.

exec sp_helptext 'your_sp_name' -- don't forget the quotes

In management studio by default results come in grid view. If you would like to see it in text view go to:

Query --> Results to --> Results to Text

or CTRL + T and then Execute.

The other answers that recommend using the object explorer and scripting the stored procedure to a new query editor window and the other queries are solid options.

I personally like using the below query to retrieve the stored procedure definition/code in a single row (I'm using Microsoft SQL Server 2014, but looks like this should work with SQL Server 2008 and up)

SELECT definition 
FROM sys.sql_modules 
WHERE object_id = OBJECT_ID('yourSchemaName.yourStoredProcedureName')

More info on sys.sql_modules:

https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-sql-modules-transact-sql

I guess this is a better way to view a stored procedure's code:

sp_helptext <name of your sp>

In case you don't have permission to 'Modify', you can install a free tool called "SQL Search" (by Redgate). I use it to search for keywords that I know will be in the SP and it returns a preview of the SP code with the keywords highlighted.

Ingenious! I then copy this code into my own SP.

The option is called Modify:

This will show you the T-SQL code for your stored procedure in a new query window, with an ALTER PROCEDURE ... lead-in, so you can easily change or amend your procedure and update it

This is another way of viewing definition of stored procedure

SELECT OBJECT_DEFINITION (OBJECT_ID(N'Your_SP'))

You can view all the objects code stored in the database with this query:

    USE [test] --Database Name
SELECT
    sch.name+'.'+ob.name AS       [Object], 
    ob.create_date, 
    ob.modify_date, 
    ob.type_desc, 
    mod.definition
FROM 
     sys.objects AS ob
     LEFT JOIN sys.schemas AS sch ON
            sch.schema_id = ob.schema_id
     LEFT JOIN sys.sql_modules AS mod ON
            mod.object_id = ob.object_id
WHERE mod.definition IS NOT NULL --Selects only objects with the definition (code)

Use this query:

SELECT object_definition(object_id) AS [Proc Definition]
FROM sys.objects 
WHERE type='P'