Viewing and Modifying Stored Procedures

SQL Server 2000 maintains the source code for stored procedures in the system catalog table syscomments . You can view this information directly by querying the text column of the syscomments table, or by using the system procedure sp_helptext (see Listing 28.6). Note that in SQL Server 2000, the text column is a computed column. The real source code for the proc is stored in a binary format in the ctext column.

Listing 28.6 Viewing Code for a Stored Procedure with sp_helptext

 exec sp_helptext title_authors go Text --------------------------------------------------------------- CREATE PROCEDURE title_authors AS SELECT a.au_lname, a.au_fname, t.title FROM titles t INNER JOIN  titleauthor ta ON t.title_id = ta.title_id RIGHT OUTER JOIN  authors a ON ta.au_id = a.au_id RETURN 

By default, all users have permission to execute sp_helptext to view the SQL code for the stored procedures in a database. If you want to protect the source code of your stored procedures and keep its contents from prying eyes, you can create a procedure using the WITH ENCRYPTION option. When this option is specified, the source code stored in the syscomments table is encrypted.

In versions of SQL Server prior to 6.0, when a developer wanted to prevent users from viewing the source code for stored procedures, he simply set the text field in the syscomments table for the stored procedure to null. This worked fine until a DBA tried to run the SQL Server 6.0 upgrade facility, which needed to extract the source code from syscomments to re-create the procedures under the new version. Without the source code in the database, the procedures were not migrated and the developers had to dig out the original source code to re-create the procedures. Recognizing this problem, Microsoft implemented the ability to encrypt stored procedures in version 6.0. This allows programmers to protect source code, while keeping it in the database so the upgrade process can re-create the stored procedures from the encrypted code.

NOTE

If you use encryption when creating your stored procedures, be aware that while SQL Server can internally decrypt the source code, no mechanisms exist for the user or for any of the end user tools to decrypt the stored procedure text for display or editing. With this in mind, make sure that you store a copy of the source code for those procedures in a file in case you need to edit or re-create them.

Also, if you use the WITH ENCRYPTION option, you can no longer use the Transact-SQL Debugger on the encrypted stored procedure. Don't use the WITH ENCRYPTION option unless you have a good reason to hide the stored procedure code.

You can also view the text of a stored procedure using the ANSI INFORMATION_SCHEMA view routines ( INFORMATION_SCHEMA views are discussed in more detail later in this chapter). The routines view is an ANSI standard view that is partially based on the syscomments table and provides the source code for the stored procedure in the routine_description column. The following example uses the INFORMATION_SCHEMA.routines view to display the source code for the title_authors stored procedure:

 select routine_definition  from INFORMATION_SCHEMA.routines where routine_name = 'title_authors' go routine_definition --------------------------------------------------------------------------- CREATE PROCEDURE title_authors AS SELECT a.au_lname, a.au_fname, t.title    FROM titles t    INNER JOIN titleauthor ta ON t.title_id = ta.title_id    RIGHT OUTER JOIN authors a ON ta.au_id = a.au_id RETURN 

You can modify the text of a stored procedure using the ALTER PROCEDURE statement. The syntax for ALTER PROCEDURE is the same as for CREATE PROCEDURE (see Listing 28.7). This new feature, introduced in version 7.0, has a couple of advantages over dropping and re-creating the procedure to modify it. The main advantage is that you don't have to drop the procedure first to make the change. The second advantage is that because you don't have to drop the procedure, you don't have to worry about reassigning permissions to it.

Listing 28.7 Modifying a Stored Procedure Using ALTER PROCEDURE

 ALTER PROCEDURE title_authors @state char(2) = '%' AS SELECT a.au_lname, a.au_fname, t.title, t.pubdate    FROM titles t    INNER JOIN titleauthor ta ON t.title_id = ta.title_id    RIGHT OUTER JOIN authors a ON ta.au_id = a.au_id    where state like @state RETURN 

The main disadvantage of using ALTER PROCEDURE instead of dropping and re-creating the stored procedures is that the date of the procedure change is not recorded in the database catalogs. As a DBA, it is difficult to tell which procedures have been modified since they were created, and you cannot selectively extract the code for only the stored procedures that have been updated.

In addition to sp_helptext , SQL Server 2000 provides two GUI-based applications for creating, viewing, and modifying stored procedures: Enterprise Manager and Query Analyzer.

To edit a stored procedure in Enterprise Manager, right-click on the procedure name and select the Properties option (see Figure 28.1).

Figure 28.1. Invoking the stored procedure editor in Enterprise Manager.

As you'll see, the editor in Enterprise Manager is not very elegant. It lacks a search and replace function and has no ability to save the stored procedure code to a file. The one nice feature is the ability to check the stored procedure syntax before applying changes (see Figure 28.2).

Figure 28.2. Running the stored procedure syntax check in Enterprise Manager.

Changes will be applied when clicking on the Apply or OK button at the bottom of the Stored Procedures Properties window. Enterprise Manager applies changes to the stored procedure by using ALTER PROCEDURE . The creation date will not be updated. If you want to modify the stored procedure by dropping and re-creating it, use Query Analyzer. Besides having a full-featured editor as well as a built-in SQL Debugger (see Chapter 6, "SQL Server Query Analyzer and SQL Debugger"), Query Analyzer provides options for extracting the stored procedure source code to modify and apply changes. It will generate code to create, alter, or drop the selected stored procedure. You can script the stored procedure source code to a new window, to a file, or to the Windows Clipboard by right-clicking on the stored procedure name in the Object Browser and choosing the appropriate option (see Figure 28.3).

Figure 28.3. Extracting stored procedure source code to an editor window.

Query Analyzer provides a number of options for extracting the stored procedure source code. The script generated can automatically include the command to check for the existence of the object and automatically drop it before executing the CREATE PROCEDURE command. You can also choose to include the commands to reset permissions when the stored procedure is re-created. To ensure these features are included, make sure the following options are checked:

• Generate Transact-SQL to remove referenced component. Script tests for existence before attempting to remove component.

• Script object-level permissions.

You can set the scripting options by choosing the Script tab in the Options dialog box. Invoke the Options dialog box by selecting Tools, Options; by pressing Ctrl+Shift+O; or by right-clicking on the procedure name in the Object Browser and choosing the Scripting Options menu option (see Figure 28.4).

Figure 28.4. Setting scripting options in Query Analyzer.

Listing 28.8 shows an example of the script generated by Query Analyzer when the options selected in Figure 28.4 are in effect. The changes can be applied by simply executing the SQL script in Query Analyzer.

Listing 28.8 Example of Stored Procedure-Creation Script Generated by Query Analyzer

[View full width]

 SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO /****** Object:  Stored Procedure dbo.title_authors    Script Date: 4/15/2001 8:14:15 PM  ******/ if exists (select * from sysobjects where id = object_id('dbo.title_authors')                                     and sysstat & 0xf = 4)        drop procedure dbo.title_authors GO CREATE PROCEDURE title_authors @state char(2) = '%' AS SELECT a.au_lname, a.au_fname, t.title    FROM titles t    INNER JOIN  titleauthor ta ON t.title_id = ta.title_id    RIGHT OUTER JOIN  authors a ON ta.au_id = a.au_id    where state like @state RETURN GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO GRANT EXECUTE ON dbo.title_authors TO public GO 

TIP

When creating a new stored procedure in Query Analyzer, the procedure will not show up in the Stored Procedures folder in the Object Browser unless you right-click on the Procedures Folder and choose the Refresh option.