Get the results of sp_helptext as a single string

Question

I'm running a query using "EXEC sp_helptext Object", but it returns multiple lines with a column name Text. I'm trying to concatenate that value into a single string but I'm having trouble trying to figure out the best way to do it using T-SQL.

Answer 1

You can try something like this

DECLARE @Table TABLE(
     Val VARCHAR(MAX)
)

INSERT INTO @Table EXEC sp_helptext 'sp_configure'

DECLARE @Val VARCHAR(MAX)

SELECT  @Val = COALESCE(@Val + ' ' + Val, Val)
FROM    @Table

SELECT @Val

This will bring back everything in one line, so you might want to use line breaks instead.

Answer 2

Assuming SQL Server 2005 and above (which is implied by varchar(max) in astander's answer), why not simply use one of these

SELECT OBJECT_DEFINITION('MyObject') 

SELECT definition FROM sys.sql_modules WHERE object_id = OBJECT_ID('MyObject')