Get sp_helptext results as one line

Question

I run the query using the "EXEC sp_helptext Object", but it returns a few rows with the column name "Text". I am trying to combine this value into one line, but I am having trouble finding the best way to do this 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 return only one line, so you can use line breaks instead.

Answer 2

Assuming SQL Server 2005 and above (which implies varchar (max) in the user response), why not just use one of these

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

Answer 3

It's not very glamorous, but it works ...

 DECLARE @Table TABLE( Val VARCHAR(MAX) ) INSERT INTO @Table EXEC sp_helptext 'sp_configure' DECLARE @Val VARCHAR(MAX) SET @Val = '' SELECT @Val = @Val + REPLACE(REPLACE(REPLACE(Val, CHAR(10), ''), CHAR(13), ''), CHAR(9), '') FROM @Table -- Replaces line breaks and tab keystrokes. SELECT @Val