1

So if I want to open a table through query I usually do like

select * from databaseschema.dbo.tablename

and it displays the results in results window at the bottom of New Query window where I have typed the query.

If I want to open a proc, I have to go to that schema, open stored procedures, right click on proc and click modify which is when it opens in new query window in SSMS.

I want a statement (like mentioned in the first paragraph) where I don't have to do the manual selection through my mouse searching for the proc manually (mentioned in second paragraph) and I can just run the statement and it will open the proc in a new query window.

Is it possible?

Marko Ivkovic
  • 1,262
  • 1
  • 11
  • 14
Damian Hale
  • 11
  • 1
  • Does this answer your question? [How to view the stored procedure code in SQL Server Management Studio](https://stackoverflow.com/questions/8733088/how-to-view-the-stored-procedure-code-in-sql-server-management-studio) – KRM Jun 24 '21 at 20:11

1 Answers1

0

yes, you can use object_definition(object_id)

use databaseName;
select name, object_definition(object_id) 
from sys.procedures
where name = 'procedureName'

don't forget to replace databaseName and procedureName by the real value

Edit : I check documentation it seems syntax is

 SELECT OBJECT_DEFINITION (OBJECT_ID(N'procedureName')) AS [Object Definition];
DonKnacki
  • 427
  • 4
  • 11
  • it shows me the results in results pane. 2 columns. column NAME shows me the proc name. 2nd column which has header (No Column Name) shows me - Batch submitted through debugger and so on....... – Damian Hale Jun 24 '21 at 18:57
  • Strange, it does work with my version of sql server. I checker [documentation](https://learn.microsoft.com/fr-fr/sql/t-sql/functions/object-definition-transact-sql?view=sql-server-ver15) it seems syntax is `SELECT OBJECT_DEFINITION (OBJECT_ID(N'procedureName')) AS [Object Definition]; ` – DonKnacki Jun 24 '21 at 20:27