Obtain the Stored Procedure content (downmoon) based on the SQL stored procedure name)

This statement applies to ms SQL Server 2000/2005

Set
Quoted_identifier
On
 

Go


Set
Ansi_nulls
On
 

Go



--
Getcontentbyprocedurename '[DBO]. [stored procedure name]'


Create
 
Procedure
Getcontentbyprocedurename

(
@ Procedurename
 
Nvarchar
(
500
))

As
 


If
 
Exists
(
Select
 
*
 
From
DBO. syscomments
Where
ID
=
Object_id
(N
''
+
@ Procedurename
+
''
))

Select
C.
Text
, C. encrypted, C.
Number
, Xtype
=
Convert
(
Nchar
(
2
), O. xtype ),

 
Datalength
(C.
Text
),
Convert
(
Varbinary
(
8000
), C.
Text
),
0
 
From
DBO. syscomments C, DBO. sysobjects o


Where
O. ID
=
C. ID
And
C. ID
=
 
Object_id
(N
''
+
@ Procedurename
+
''
)

Order
 
By
C.
Number
, C. colid
Option
(Robust
Plan
)


Go


Set
Quoted_identifier
Off
 

Go


Set
Ansi_nulls
On
 

Go