Microsoft® SQL Server® 2012 Bible

434

Part IV: Programming with T-SQL in SQL Server

USE AdventureWorks2012

GO

ALTER PROCEDURE Sales.uspGetCurrencyInformation

AS

SELECT CurrencyCode, Name, ModifiedDate

FROM Sales.Currency;

GO

Drop

The DROP statement removes the stored procedure from the server. It is a good idea to

maintain stored procedure code in some sort of version control system in addition to the

database engine. Accidentally dropping a stored procedure without having a way to easily

retrieve it can create time-consuming work.

DROP PROCEDURE Sales.uspGetCurrencyInformation;

Viewing Stored Procedures

After a stored procedure has been created, you can view the code within the database

engine in a few ways.

SQL Server Management Studio (SSMS) GUI

The fi rst option is to use the SQL Server Management Studio (SSMS) graphical user

interface.

1. From SQL Server Management Studio, open the Object Explorer by clicking F8 or by
   going to the View menu and selecting Object Explorer.


2. Within Object Explorer, click Connect, and provide connection information for the
   server where you created the stored procedure.


3. Expand the Databases folder, then the Database the stored procedure was created
   in, and then the Programmability Folder. From here, there is a Stored Procedures
   folder. Expand this folder, and the stored procedure should be visible. If it is not,
   right-click and select Refresh.


4. Right-click the stored procedure, and mouse-over Script Stored Procedure As. The
   submenu should display, enabling you to select CREATE To. The options include New
   Query Editor Window, File, Clipboard, and Agent Job. Choosing New Query Editor
   Window adds a new query tab to SSMS with the stored procedure code scripted in it.

EXECUTE sp_HelpText

The second option for viewing a stored procedure is to execute a system stored procedure

called sp_helptext. The stored procedure code is visible from a system view called sys

c17.indd 434c17.indd 434 7/30/2012 5:39:34 PM7/30/2012 5:39:34 PM

http://www.it-ebooks.info