Quick way to modify MS SQL stored procedure

Question

I need to edit Microsoft SQL stored procedures frequently and i found the traditional way (Open MSSMS -> expand the databases tree, expand the stored procedures tree and define filter by the name of the SP) very long. i am looking for a way (command line) like "sp_helptext " but a way that will actually open for me the stored procedure for edit.

Thanks.

Solution 1

Check out Redgate SQL Prompt. With this you can right click the Stored Proc's name and click "ALTER"

RED GATE SQL PROMPT

Solution 2

There is no direct command like

MODIFY dbname.schemaname.spname

You have 3 options that use TSQL, apart from the traditional GUI way using SSMS

EXEC sp_helptext dbname.schemaname.spname';

SELECT OBJECT_DEFINITION (OBJECT_ID(dbname.schemaname.spname'));

SELECT definition
FROM sys.sql_modules
WHERE object_id = (OBJECT_ID(dbname.schemaname.spname'));

Unfortunately, all these options will result in loss of formatting.

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 am afraid cut and paste is your only option.

Raj

Solution 3

There is a way: I develop SSMSBoost - add-in for SSMS. It allows to open object scripts directly from SQL Editor.

1. In SQL Editor Place cursor on stored procedure name

2. Hit F2 and procedure will be scripted in new window.

Hitting Ctrl-F2 would locate object in object explorer tree, so you can use further SSMS commands right-clicking it.

Hope this helps.