Logo
Windows XP
Windows Vista
Windows 7
Windows Azure
Windows Server
Windows Phone
EPL Standings
 
 
Windows Server

SQL Server 2008 R2 : Creating and Managing Stored Procedures - Viewing Stored Procedures

2/6/2013 4:30:49 PM

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.

Other -----------------
- SQL Server 2008 R2 : Creating and Managing Stored Procedures - Deferred Name Resolution
- Using Microsoft SharePoint with Microsoft Dynamics CRM Functions (part 2) - Displaying Data Using BDC in Microsoft Office SharePoint Server
- Using Microsoft SharePoint with Microsoft Dynamics CRM Functions (part 2) - Displaying Data Using BDC in Microsoft Office SharePoint Server
- Using Microsoft SharePoint with Microsoft Dynamics CRM Functions (part 1) - Displaying Data in SharePoint Using the List Web Part for Microsoft Dynamics CRM 4.0
- Microsoft Exchange Server 2007 : Single Copy Clusters (part 2) - Installing Exchange Server 2007 on the Active Node
- Microsoft Exchange Server 2007 : Single Copy Clusters (part 1)
- Windows Server 2003 on HP ProLiant Servers : Logical Structure Design (part 5) - Trust Definitions
- Windows Server 2003 on HP ProLiant Servers : Logical Structure Design (part 4) - Group Policy
- Windows Server 2003 on HP ProLiant Servers : Logical Structure Design (part 3) - Naming Standards
- Windows Server 2003 on HP ProLiant Servers : Logical Structure Design (part 2) - Forest Structure, OU Structure
- Windows Server 2003 on HP ProLiant Servers : Logical Structure Design (part 1) - Domain and OU Structure
- Microsoft Dynamics GP 2010 : Preventing Errors in Dynamics GP - Ensuring proper year-end closing by checking Posting Types
- Microsoft Dynamics GP 2010 : Preventing Errors in Dynamics GP - Preventing account selection errors with Chart Segment names
- Monitoring Windows Small Business Server 2011 : Using Windows SBS Console Monitoring (part 3) - Creating and Viewing Reports
- Monitoring Windows Small Business Server 2011 : Using Windows SBS Console Monitoring (part 2) - Using Notification Settings
- Monitoring Windows Small Business Server 2011 : Using Windows SBS Console Monitoring (part 1) - Using the Network Essentials Summary
- System Center Configuration Manager 2007 : Operating System Deployment - Boot Images
- System Center Configuration Manager 2007 : Operating System Deployment - Site Systems
- BizTalk Server 2006 : Pipeline Component Best Practices and Examples - The Databased Disassembler
- BizTalk Server 2006 : Pipeline Component Best Practices and Examples - Using PGP (part 2) - PGP Decode Component
 
 
Most view of day
- Working with the Windows Home Server Registry : Starting the Registry Editor, Navigating the Registry
- Windows Phone 8 : Working with the Windows Phone Software (part 7) - Removing Multimedia Content - Removing a Video from Your Phone
- Windows Phone 7 : 3D Game Development (part 2) - Rendering 3D Primitives
- Microsoft Dynamic CRM 4 : Data Migration (part 3) - Creating a CRM Adapter Publisher
- Windows Server 2008 R2 high-availability and recovery features : Installing and Administering Failover Clustering (part 7) - Create shared folder on cluster, Testing Failover of Cluster
- What's new and improved in SharePoint 2013 : Previewing search documents, Using the Community Site template
- Windows Server 2012 : Managing networking using Windows PowerShell (part 1) - Identifying networking cmdlets
- Microsoft Visio 2010 : Working with Text (part 3) - Text Resizing Behavior
- Microsoft Dynamics CRM 4 : Digital Phone Integration (part 1)
- Microsoft Visio 2010 : Introducing Data Graphics (part 1) - What Is a Data Graphic?
Top 10
- Windows Server 2012 : DHCP,IPv6 and IPAM - Exploring DHCP (part 3) - Creating IPv4 DHCP Scopes
- Windows Server 2012 : DHCP,IPv6 and IPAM - Exploring DHCP (part 2) - Installing DHCP Server and Server Tools
- Windows Server 2012 : DHCP,IPv6 and IPAM - Exploring DHCP (part 1)
- Windows Server 2012 : DHCP,IPv6 and IPAM - Understanding the Components of an Enterprise Network
- Microsoft OneNote 2010 : Using the Research and Translate Tools (part 3) - Translating Text with the Mini Translator
- Microsoft OneNote 2010 : Using the Research and Translate Tools (part 2) - Translating a Word or Phrase with the Research Pane
- Microsoft OneNote 2010 : Using the Research and Translate Tools (part 1) - Setting Options for the Research Task Pane, Searching with the Research Task Pane
- Microsoft OneNote 2010 : Doing Research with Linked Notes (part 2) - Ending a Linked Notes Session, Viewing Linked Notes
- Microsoft OneNote 2010 : Doing Research with Linked Notes (part 1) - Beginning a Linked Notes Session
- Microsoft OneNote 2010 : Doing Research with Side Notes (part 3) - Moving Side Notes to Your Existing Notes
 
 
Windows XP
Windows Vista
Windows 7
Windows Azure
Windows Server
Windows Phone
2015 Camaro