Geek Answers Handbook

A quick way to modify an MS SQL stored procedure

I often need to edit Microsoft SQL stored procedures, and I found a traditional way (Open MSSMS -> expand the database tree, expand the stored procedure tree and define a filter by the name of SP) for a very long time. I am looking for a method (command line) such as "sp_helptext", but a method that actually opens a stored procedure for me to edit.

Thanks.

+4
source share
6 answers

Complete the Redgate SQL query. With this, you can right-click the name of the saved Proc and press "ALTER"

RED GATE SQL PROMPT

+1
source

There is a way: I am developing SSMSBoost - an add-on for SSMS. It allows you to open object scripts directly from the SQL editor.

  • In the SQL editor, place the cursor on the name of the stored procedure.

  • Hit F2 and the procedure will be written in a new window.

Hit Ctrl-F2 will find the object in the tree of the object explorer, so you can use other SSMS commands by right-clicking on it.

Hope this helps.

+3
source

There is no direct command, for example

MODIFY dbname.schemaname.spname

You have 3 options that use TSQL, in addition to the traditional GUI method 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 of these options will result in loss of formatting.

Here you are trying to use two technologies.

  • SQL and SQLSyntax
  • SQL Management Tool

It is probably not possible to use TSQL to manage Studio Management, as you see fit. I'm afraid cut and paste is your only option.

Rajah

+2
source

Here is the code that you can use to start the sp_helptext procedure, which will return you a table of strings that you can write to a file that will create the stored procedure. You can then pass the file name to the SSMS command line to open the file directly when you open SQL Server Manager.

ex) Ssms tempFile.sql

 using (SqlConnection con = new SqlConnection ("Connection String Here")) { using (SqlCommand cmd = con.CreateCommand()) { cmd.CommandText = "sp_helptext @procName"; cmd.CommandType = CommandType.Text; cmd.Parameters.AddWithValue("procName", "Name Of Stored Proc Here"); con.Open(); using (SqlDataReader rdr = cmd.ExecuteReader()) { while (rdr.Read()) { /* You will get the CREATE PROC text here Do what you need to with it. For example, write to a .sql file */ } } } }
0
source

Never use SSMS (or the SSMS add-in) for script stored procedures for changes. Saved procs should always be opened directly from your source control. If you do not have these objects in the source control, you need to do this. SP is a code, they need to be processed as well as other code. Incorrectly editing sps scripts from outside the source control system.

0
source

Use it

 SELECT ROUTINE_DEFINITION from INFORMATION_SCHEMA.ROUTINES where ROUTINE_NAME = 'YourStoredProcedureName'

Hope this helps.

0
source

More articles:


All Articles