Microsoft® SQL Server® 2012 Bible

435

Chapter 17: Developing Stored Procedures

17

.syscomments. Sp_helptext retrieves the stored procedure code and returns it as the

result set.

EXECUTE sp_helptext 'Sales.uspGetCurrencyInformation'

Query sys.sql_modules

The third option is to query the sys.sql_modules system view.

USE AdventureWorks2012;

GO

SELECT definition

FROM sys.sql_modules

WHERE object_id = (OBJECT_ID

(

N'Sales.uspGetCurrencyInformation')

);

Use OBJECT_DEFINITION

The fourth option is to use the OBJECT_DEFINITION built-in function with the OBJECT_

ID built-in function.

USE AdventureWorks2012;

GO

SELECT OBJECT_DEFINITION (OBJECT_ID

(

N'AdventureWorks2012Sales.uspGetCurrencyInformation')

);

Because the stored procedure code is stored as multiple rows in the database metadata, it is easier to read the

returned code if the results are returned as text rather than as a grid. To change this setting, use the Query menu in

SSMS. There is a Results To option, and the options are Results to Grid, Results to Text, and Results to File. Choose

Results to Text; then execute sp_helptext, query sys.sql_modules, or call OBJECT_DEFINITION.

Alternatively, you can use keyboard shortcut Ctrl+T to change the results to text format.

Encrypting Stored Procedure Code

You can encrypt stored procedure code so that the text of the code is not visible by any of

the means previously described. Encrypting stored procedures is not a common practice,

and there are some serious drawbacks, such as the inability to publish an encrypted stored

procedure as part of SQL Server replication. There needs to be a compelling and carefully

considered justifi cation for encrypting a stored procedure. One implementation in which

this approach might be seen is in third-party software. Vendors might try to protect propri-

etary formulas or logic using stored procedure encryption.

c17.indd 435c17.indd 435 7/30/2012 5:39:34 PM7/30/2012 5:39:34 PM

http://www.it-ebooks.info