You can view the source code for stored procedures in SQL Server 2008 by querying the definition column of the object catalog view sys.sql_modules or by using the system procedure sp_helptext (see Listing 1).
Listing 1. Viewing Code for a Stored Procedure by Using sp_helptext
exec sp_helptext title_authors
go
Text
--------------------------------------------------------------
CREATE PROCEDURE title_authors
AS
BEGIN
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
END
|
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 stored procedures and keep its
contents from prying eyes, you can create a procedure by using the WITH ENCRYPTION option. When this option is specified, the source code stored in the database is encrypted.
Note
If you use encryption when creating stored
procedures, be aware that although 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, procedures created by using the WITH ENCRYPTION option cannot be published as part of SQL Server replication.
You can, however, attach a debugger to the server process and retrieve a decrypted procedure from memory at runtime.
You can also view the text of a stored procedure by using the ANSI INFORMATION_SCHEMA view routines. The routines view is an ANSI standard view that 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 specific_catalog = 'bigpubs2008'
and specific_schema = 'dbo'
and routine_type = 'Procedure'
and routine_name = 'title_authors'
go
routine_definition
-------------------------------------------------------------------------
CREATE PROCEDURE title_authors
AS
BEGIN
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
END
However, the routine_description column is
limited to only the first 4,000 characters of the stored procedure code.
A better way to view the code with a query is to use the sys.sql_modules object catalog view:
select definition
from sys.sql_modules
where object_id = object_id('title_authors')
go
CREATE PROCEDURE title_authors
AS
BEGIN
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
END
Finally, one other method of displaying the source code for a stored procedure is to use the new object_definition()
function. This function takes the object ID as a parameter. If you,
like most other people, do not know the object ID of the procedure in
question, you can use the object_id() function. The following is an example of using the object_definition() function:
select object_definition(object_id('dbo.title_authors'))
go
------------------------------------------------------------------------------
CREATE PROCEDURE title_authors @state char(2) = '%'
AS
BEGIN
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
END
Tip
If you are running these queries to display the
procedure code in a query window in SSMS, you probably need to modify
the query results options to have the procedures display correctly. From
the Query menu, select Query Options. Expand the Results item and
select Text. Enter a value up to 8192 for the Maximum Number of
Characters Displayed in Each Column setting and click OK.
You probably also want to
have the results displayed as text rather than in the grid. To make this
change, under the Query menu, select the Results To submenu and then
select Results to Text. As a shortcut, you can press Ctrl+T to switch to
Results to Text. You can press Ctrl+D to switch back to Results to
Grid.
