Microsoft® SQL Server™ 2000 Unleashed, Second Edition

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 ...