Managing Stored Procedures

Microsoft SQL Server Management Studio is the primary tool that you will use to control the environment and manage stored procedures. We will review the ways that you can use it to

• List stored procedures

• View code of stored procedures

• Rename stored procedures

• Delete stored procedures

• List dependent and depending objects

Listing Stored Procedures

The easiest way to list stored procedures in a database is to view them from Management Studio. All you need to do is to expand in the Object Browser the following nodes: server| Databases | database| Programmability | Stored Procedures. We have demonstrated that in the previous chapter.

The alternative is the traditional way based on Transact-SQL. SQL Server is delivered with the system stored procedure sp_stored_procedures.

 Exec sp_stored_procedures 

The stored procedure sp_stored_procedures retrieves a list of stored procedures from the sys.objects system view in the database. If you want to see the sys.objects table's contents, execute the following statement:

 Select * from sys.objects 

You can see the results in Figure 2-1.

To see just user-defined stored procedures, you need to filter the database objects with Type set to ‘P’:

 Select * from sys.objects where type = ' P' 

Figure 2-1: Objects in sys.objects

It might look like overkill to list stored procedures this way and you may be right. However, it is useful to know how SQL Server stores and manages stored procedures, because sooner or later you will need to open a back door to automate something in the environment.

Note 

In earlier versions of SQL Server, a list of database objects was stored in dbo.sysobjects. In SQL Server 2005, this system table was replaced by two system views—sys.sysobjects and sys.objects. Tables are similar, but not identical. You can see in BOL or wben you execute it, tbat tbere is a couple of columns tbat are different. Microsoft plans to keep sys.objects, wbile sys.sysobjects is in tbis version for compatibility, just in case the code tbat you are porting from earlier versions to SQL Server 2005 contains references to dbo.sysobjects.

Viewing Code of Stored Procedures

I have already shown you in the previous chapter how to display code of a stored procedure in Management Studio. You can choose either the Modify option from the context-sensitive menu of a stored procedure or Script Stored Procedure As | Alter To| New Query Editor Window.

It is a little bit more difficult to display a stored procedure in the traditional way using Transact-SQL. You need to use the sp_helptext system stored procedure. The database that contains the stored procedure must be the current database, and you must supply the name of the stored procedure as a parameter:

 exec sp_helptext 'dbo.ap_EqIdByMakeModel_List' 

Note 

You can also use spjielptext to view the code of otber database objects sucb as triggers, views, defaults, and rules.

If you now want to save the code of the stored procedure, you can copy it through the Clipboard to your Query pane, or you can save the contents of the Results pane in a text file:

1. From the main menu, choose Query | Results To | Results to Text.

2. Execute sp_helptext.

3. Select File | Save Results As and specify a name for the file. Verify that the File Format is set to ANSI.

The result will be saved to an ANSI file, which you can edit in any text editor, such as Notepad.

Renaming Stored Procedures

There are several ways to change the name of a stored procedure. If you use sp_rename or a command of Management Studio, SQL Server will change the name of the object in the sys.sysobjects view but it will not affect the code of the stored procedure:

 Exec sp_rename 'Procedure2', 'Procedure_2' 

When you open the procedure with the Modify option from the context-sensitive menu, everything will look normal. But you might receive unexpected results when you try to execute the Create Procedure statement or the Alter Procedure statement after that. You will clearly see the problem if you generate the script of the stored procedure:

 USE [Asset5] GO /****** Object:  StoredProcedure [dbo] . [Procedure_2] Script Date: 02/13/2005 19:14:04 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO create proc Procedure2 as select 'Hello world' select * from dbo.EqType GO SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO 

Although the window name and comment refer to the new stored procedure name, the old stored procedure name is referenced in the only place that matters—in the Create Procedure statement.

sp_rename was designed to change the names of all database objects (including tables, views, columns, defaults, rules, and triggers). In fact, the versatility of this stored procedure is the reason the code is not changed in the previous example. The stored procedure is designed to change only the names of objects in the sys.sysobjects view.

Database objects with code such as stored procedures, views, and user-defined functions require a different strategy. It is better to drop them and create them again. Again, do not forget to change all associated objects, such as permissions, at the same time. The Alter Procedure statement cannot help us in this case, since we need to change the name of the stored procedure.

This operation is not something that you should perform very often. It could he problematic if you were to do it on a production server. SQL Server contains a procedure cache—a part of the memory where it keeps compiled versions of stored procedures. You should flush the procedure cache to force all dependent stored procedures (which refer to the stored procedure by its old name) to recompile. You can use the DBCC FREEPROCCACHE command or you can simply restart SQL Server (service) and the procedure cache will be emptied.

Deleting Stored Procedures

To delete a stored procedure from Management Studio, right-click the name of the stored procedure in the list and select Delete. Drop Procedure is a Transact-SQL statement for deleting a stored procedure. To use it, you must supply the name of the stored procedure as a parameter:

 DROP PROCEDURE dbo.Procedure_l 

Objects that are referencing the stored procedure that has been dropped will not be able to run properly after this.

Listing Dependent and Depending Objects

If you plan to perform some dramatic action, such as deleting or renaming a database object, you should first investigate which objects will be affected by the action. Microsoft SQL Server keeps a list of dependencies between objects in the sys.sysdepends system view in each database. To view this list in Management Studio:

1. Right-click the name of the database object in the Object Browser.

2. Select View Dependencies, and SQL Server will display a dialog box with a list of dependencies.

3. You can also switch between the "Objects that depend on…" and "Objects on which … depends" choices.

The system stored procedure sp_depends has a similar function. It can also return one or two result sets—one for dependent objects and one for depending objects. If you execute the following statement in the Query window,

 exec sp_depends ap_Equipment_Full 

you will see a result like that shown in figure 2-2.

Figure 2-2: Results of sp_depends

Note 

Unfortunately, you cannot completely rely on SQL Server to get a list of dependencies. It does not update the content of the sys.sysdepends view in all cases. You can reproduce and observe this behavior when, for example, you drop and re-create a table. However, in that case, SQL Server will warn you that it cannot properly update dependencies.

This problem has been a known issue since version 4.21, but since SQL Server 7.0, the problem is even more difficult to manage because of deferred name resolution (it is possible to compile some objects such as stored procedures before all objects that they reference are defined in the database). Therefore, if SQL Server displays an empty list, you should open the source code and check it!

Neither of these methods will show you dependencies between objects outside of the current database.