Get Stored Procedure Definition– DAC way


By connection the instance with DAC (Dedicated Administrator Connection), we can use the following code snippet to get stored procedure definition.

 

To connect to a server using the DAC:
In SQL Server Management Studio, File>>>New>> click Database Engine Query.
type ADMIN: in front of your instance name.

https://technet.microsoft.com/en-us/library/ms178068%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396

 

The query:

  

 ---DAC connection
 select	p.name,	cast(sov.imageval as varchar(MAX)) sp_def, len(cast(sov.imageval as varchar(MAX)))
from sys.procedures p
inner join sys.sysobjvalues sov on p.object_id = sov.objid
 WHERE cast(sov.imageval as varchar(MAX)) like '%searchString%'

 --http://improve.dk/where-does-sql-server-store-the-source-for-stored-procedures/

 
Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s