Retrieve Stored Procedure Code

Hi,
I am trying to retrieve the code for some stored procedures. I was able to
select the routine definition from the information_schema.routines for the
procedures that had less than 4000 characters, but I can't get the larger
procedures. Does anyone know how to pull the entire stored procedure if it
has more than 4000 characters?
ThanksChris F wrote:
> Hi,
> I am trying to retrieve the code for some stored procedures. I was
> able to select the routine definition from the
> information_schema.routines for the procedures that had less than
> 4000 characters, but I can't get the larger procedures. Does anyone
> know how to pull the entire stored procedure if it has more than 4000
> characters?
> Thanks
That system table syscomments contains multiple rows that you can pull
back. Or you can simply use sp_helptext (or examine its code using
sp_helptext to see how it pulls the data back).
David Gugick
Quest Software
www.imceda.com
www.quest.com|||You can use sp_helptext or you can pull it from syscomments system table.
Example:
use northwind
go
declare @.s1 varchar(8000)
declare @.s2 varchar(8000)
set @.s1 = 'create procedure dbo.p1 as select '''
set @.s2 = replicate('a', 7999) + ''''
exec(@.s1 + @.s2)
go
select
*
from
syscomments
where
[id] = object_id('[dbo].[p1]')
order by
number, colid
go
exec sp_helptext '[dbo].[p1]'
go
drop procedure dbo.p1
go
AMB
"Chris F" wrote:

> Hi,
> I am trying to retrieve the code for some stored procedures. I was able t
o
> select the routine definition from the information_schema.routines for the
> procedures that had less than 4000 characters, but I can't get the larger
> procedures. Does anyone know how to pull the entire stored procedure if i
t
> has more than 4000 characters?
> Thanks|||Thanks. I appreciate the help.
"David Gugick" wrote:

> Chris F wrote:
> That system table syscomments contains multiple rows that you can pull
> back. Or you can simply use sp_helptext (or examine its code using
> sp_helptext to see how it pulls the data back).
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>