From a Query Window, can a Stored Procedure be opened into another Query Window?

Question

Is there command inside a Query Window that will open a stored procedure in another Query Window?

i.e. MODIFY dbo.pCreateGarnishmentForEmployee

I am using SQL Server management Studio 2005 and Red Gate's SQL Prompt.

Currently I have to do the follwowing multiple steps:

Open Object Explorer Navigate Programmability | Stored Procedure Right Click the Stored Procedure name Select Modify

A Query Window will open up with the ALTER PROCEDURE.

As I mentioned above, what I would like to do is from a Query Window type in something to the effect of

MODIFY dbo.pCreateGarnishmentForEmployee

Answer 1

You are trying to mix two technologies here.

1. SQL and SQLSyntax
2. The SQL Management Tool

It is probably not possible to use TSQL to manipulate the Management Studio, which is what you appear to want. I suspect cut and paste is your only option.

Answer 2

I think that the only way that I'm aware of that produces an outcome similar to what you're asking for is running sp_helptext against your stored procedure name

sp_helptext 'dbo.pCreateGarnishmentForEmployee'

which will output the text as a resultset. Then click on the column header and copy/paste the resultset into the query window. You'll also need to change the

CREATE PROCEDURE ...

to

ALTER PROCEDURE ...

This method does not always produce the nicely formatted layout of your stored procedure however, so bear this in mind.