get the text of a stored procedure into a variable in SQL Server

Question

I want to loop through several stored procs and extract one string form each of them to use in another procedure (basically the 4-part remote server string)

So I can get the stored procs list from SysObjects (with Type = P) into a table, then I can loop or cursor through that table variable calling sp_helptext on each one.

But how can I get the text result of sp_helptext into a variable so I can do a CharIndex on the word 'BOCTEST' etc?

Is there a table like sys.procedures that stores the text.

Answer 1

The portable way is to use the ANSI/ISO view INFORMATION_SCHEMA.ROUTINES, but you'll only get the first 4000 characters of the stored procedure definition:

declare @source_code varchar(max)

select @source_code = t.ROUTINE_DEFINITION
from information_schema.routines t
where specific_schema = 'owner-schema'             -- e.g., dbo
  and specific_name   = 'my_stored_procedure_name' -- your stored procedure name here

Or you can use the system view sys.sql_modules in the same vein:

declare @source_code varchar(max)

select @source_code = definition
from sys.sql_modules
where object_id = object_id('dbo.my_stored_procedure_name')

Or, the simplest way:

declare @source_code varchar(max)
set @source_code = object_definition( 'dbo.my_stored_procedure_name' )