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.

you have to "modify" it. – Jeremy Holovacs Jan 04 '12 at 19:57 — Jeremy Holovacs, Jan 04 '12 at 19:57

score 164 · Answer 1 · answered Jun 12 '13 at 12:01

164

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

sp_helptext <name of your sp>

answered Jun 12 '13 at 12:01

Kniganapolke

5,073
5
22
19

14

This approach works best when the results are displayed to text ([Ctrl+T keyboard shortcut](http://msdn.microsoft.com/en-us/library/ms174205.aspx)). – Florin Dumitrescu May 21 '14 at 08:17
3

@FlorinDumitrescu - I prefer to leave it in grid mode and do the following which is extremely fast and is now 2nd nature for me: sp_helptext YourSPName, F5, click the header column "Text", ctrl-c, click anywhere in query, ctrl-a, ctrl-v, ctrl-home, ctrl-r. The reason I like this is I get syntax highlighting and I don't have to scroll to the beginning of the file. (And it's slightly better than ctrl-t because it doesn't prepend the file with "text ---------------".) – TTT Apr 29 '16 at 20:36

score 61 · Accepted Answer · edited Jan 28 '21 at 00:30

61

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.

edited Jan 28 '21 at 00:30

Pang

9,564
146
81
122

answered Jan 04 '12 at 19:54

SQLMenace

132,095
25
206
225

9

I prefer the 'Script stored procedure as CREATE' instead of 'Modify' when I am just looking at the procedure so I don't accidentally change the procedure. – simon Jan 05 '12 at 03:52

marc_s · Answer 3 · 2023-07-25T08:42:28.360

42

The option is called Modify:

Click on Database --> Programmability --> Stored Procedure .

Here you can right click any of the stored procedures, and click modify.

Be extra careful not to modify it, when you only want to view it!

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

edited Jul 25 '23 at 08:42

answered Jan 04 '12 at 19:55

marc_s

732,580
175
1,330
1,459

2

If modify is disabled? I do not have permissions to modify – Alexandr Nov 04 '14 at 16:18

score 21 · Answer 4 · answered Feb 24 '15 at 05:28

21

This is another way of viewing definition of stored procedure

SELECT OBJECT_DEFINITION (OBJECT_ID(N'Your_SP'))

answered Feb 24 '15 at 05:28

sqluser

5,502
7
36
50

1

the above one will not give the entire procedure script – Smart003 Nov 22 '16 at 05:54
@Smart003, yes it does. If you have difficulties having all characters to be displayed as a grid result refer to this http://stackoverflow.com/questions/11897950/how-do-you-view-all-text-from-an-ntext-or-nvarcharmax-in-ssms – sqluser Nov 22 '16 at 06:07
2

Thanks - very useful when repository locked for update/processing. – Hilary Dec 05 '18 at 09:16

score 14 · Answer 5 · edited Jan 28 '21 at 00:33

14

Use this query:

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

edited Jan 28 '21 at 00:33

Pang

9,564
146
81
122

answered Oct 17 '14 at 10:26

ANIL KUMAR DUBEY

141
1
2

score 14 · Answer 6 · edited Jan 17 '20 at 18:56

14

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.

edited Jan 17 '20 at 18:56

LostNomad311

1,975
2
23
31

answered Feb 21 '17 at 04:56

Ryan

615
8
19

score 12 · Answer 7 · answered May 04 '17 at 17:48

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://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-sql-modules-transact-sql

Seems to be the best solution since every other approach will mangle long lines in definition. However it does not account for renamed modules: ``` create procedure dbo.SpRenameDemo as go exec sp_rename @objname=N'dbo.SpRenameDemo' , @newname=N'SpRenameDemo_Oops'; go select * from sys.sql_modules where object_id=object_id(N'dbo.SpRenameDemo'); select * from sys.sql_modules where object_id=object_id(N'dbo.SpRenameDemo_Oops'); ``` — blaz, Nov 30 '20 at 16:54

score 3 · Answer 8 · answered Dec 28 '18 at 12:50

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)

score 2 · Answer 9 · edited Jan 28 '21 at 00:37

2

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.

edited Jan 28 '21 at 00:37

Pang

9,564
146
81
122

answered Feb 25 '13 at 20:27

iDriveTheLateCar

31
4

score 1 · Answer 10 · answered Feb 28 '21 at 09:15

1

This is the better way :

SELECT object_definition(object_id)
FROM sys.objects 
WHERE type='p' and name='SP_Name'

answered Feb 28 '21 at 09:15

Nima Habibollahi

360
5
16

This is the answer I'm looking for. I have multiple stored procedures in server and local databases. I'm using this query to re-create the stored procedures. ```SELECT name,object_definition(object_id) FROM sys.objects WHERE type='p' and name LIKE'sp_%'``` – Ashan Jan 11 '22 at 11:31

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

10 Answers10

Linked

Related