How to view the stored procedure code in SQL Server Management Studio

Question

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

User · Accepted Answer

Right click on the stored procedure and select Script Stored Procedure as | CREATE To | New Query Editor Window / Clipboard / File.

You can also do Modify when you right click on the stored procedure.

For multiple procedures at once, click on the Stored Procedures folder, hit F7 to open the Object Explorer Details pane, hold Ctrl and click to select all the ones that you want, and then right click and select Script Stored Procedure as | CREATE To.

User · Answer

In case you don t have permission to  Modify   you can install a free tool called  quot SQL Search quot   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

User · Answer

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

User · Answer

Use this query  SELECT object definition object id  AS  Proc Definition  FROM sys objects  WHERE type  P

User · Answer

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

User · Answer

This is another way of viewing definition of stored procedure  SELECT OBJECT DEFINITION  OBJECT ID N Your SP

User · Answer

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

User · Answer

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

User · Answer

I guess this is a better way to view a stored procedure s code   sp helptext  lt name of your sp gt

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

Examples related to sql-server

Examples related to tsql

Examples related to ssms