How do I format the output from OBJECT_DEFINITION so that it looks the same as sp_helptext?

Question

object_definition and sp_helptext both return the source code of a stored procedure - but how can I use object_definition and get the source code including the line breaks as the sp_helptext currently does?

for example in the code below I create a stored procedure:

if OBJECT_ID('usp_radhe') is not null
   drop procedure usp_radhe
go
-- this procedure is just a test
-- it just returns a date in the past
-- how will I get its source code?
create procedure usp_radhe as 
begin
select dateadd(dd,-31,GETDATE()) 
end 

using sp_helptext I get a nice view of the source code:

sp_helptext 'usp_radhe'

using object_definition I get the source code in a single line and that is not good for me:

select OBJECT_DEFINITION(object_id('usp_radhe'))

Answer 1

I had similar problem. First turn on this option of SSMS - it is turned off by default:

SSMS/Tools/Options/Query results/SQL Server/Results to grid/Retain CR/LF on copy or save

Close SSMS, and run it again to get it work.

I use this query to get the full text of object definitions:

create view v_get_text_of_objects as

select top 100000
  [name]
, definition = replace(replace(object_definition(object_id),'create view','alter view'),'create proc','alter proc')
, len = len(object_definition(object_id)) 
, type
, type_desc
, create_date   
, modify_date
from  sys.objects
where object_definition(object_id) like '%your text to find in sp%'
order by type, create_date desc

I like to display LEN of the sp text, so that I am sure it is not cropped.

Answer 2

Update

With the release of SSMS 18.2 on 2019-07-25, a new option became available. The option itself has existed for a long time, but with a change made in this release it became viable to use for the purpose requested here.

The specific change was increasing the number of characters retrieved when using "Result to Text" (by 8x). The documentation states (emphasis mine):

Allow more data to be displayed (Result to Text) and stored in cells (Result to Grid). SSMS now allows up to 2M characters for both (up from 256 K and 64 K, respectively). This also addressed the issue of users not able to grab more than 43680 chars from the cells of the grid.

Now the following displays as expected in "Results to Text":

SET NOCOUNT ON; SELECT OBJECT_DEFINITION(OBJECT_ID(N'sys.sp_helptext'));

By default, the results are "column aligned" and "include column headers". With this combination of options, a line of dashes is used between the column headers and the results. The problem is that being column aligned, the dashes extend the full amount of what that column could possibly retrieve, which is likely to be 2,097,152 dashes (i.e. 2 MB worth). SSMS doesn't like super long lines and freezes for a second when scrolling up for that line to be in view.

You can get rid of that line by changing either (or both) of those two aforementioned options: either uncheck "Include column headers in the result set" and/or set "Output format" to "comma delimited". If you want these changes to be the new defaults (i.e. be remembered for next time), then you need to make the changes by going to Tools -> Options -> -> Query Results -> SQL Server -> Results to Text. If you want the changes to only be temporary, then right-click in the query editor tab/window, select "Query Options..." and go to Results -> Text.

I don't remember what the default value for "Maximum number of characters displayed in each column" was, but if it's not 2097152, then I suggested making that change the new default (i.e. via the "Tools" menu).

If the code is truncated due to being more than 2,097,152 bytes (characters in most cases), then a) yikes!, and b) use the XML method described below.

Original Answer

If you don't mind an extra line at the very top and very bottom that should be ignored, you can use the following to display the full text, as text and not as a result set:

SELECT 1 AS [Tag], 0 AS [Parent], NCHAR(13) + NCHAR(10) +
       OBJECT_DEFINITION(OBJECT_ID(N'sys.sp_helptext')) AS [Code!1!!CDATA]
FOR XML EXPLICIT;

It works the same across all versions starting with SQL Server 2005 (well, I assume from seeing that it worked on 2005, 2017, and 2019 CTP 2.2). I used a "CDATA" section (which, in turn, requires using the EXPLICIT mode of FOR XML) so that XML "special" characters <, >, &, and " would not be escaped as <, >, &, and ", respectively.

If you execute the T-SQL shown above, you will get back a one row, one column result set of an XML value. Click on the underlined XML value and it will open up in a new tab showing the full, properly formatted text (along with a first line of <Code><![CDATA[ and a final line of ]]></Code>):

<Code><![CDATA[
create procedure sys.sp_helptext
@objname nvarchar(776)
,@columnname sysname = NULL
as
set nocount on
declare @dbname sysname
... {redacted for space} ...
select Text from #CommentText order by LineId
CLOSE  ms_crs_syscom
DEALLOCATE  ms_crs_syscom
DROP TABLE  #CommentText
return (0) -- sp_helptext
]]></Code>

Depending on the size of the item you are "displaying", you might need to increase the SSMS setting for how much XML data it pulls down (else you might see truncated results). Go to:

Tools | Options | Query Results | SQL Server | Results to Grid

Then, under "Maximum Characters Retrieved", set "XML data" to (options are: 1 MB, 2 MB, 5 MB, and Unlimited). Then click the "OK" button.

Answer 3

sp_helptext 'sp_helptext'
    --If carriage return found  
    IF @CurrentPos != 0  
    begin  
        /*If new value for @Lines length will be > then the  
        **set length then insert current contents of @line  
        **and proceed.  
        */  
        while (isnull(LEN(@Line),0) + @BlankSpaceAdded + @CurrentPos-@BasePos + @LFCR) > @DefinedLength  
        begin  
            select @AddOnLen = @DefinedLength-(isnull(LEN(@Line),0) + @BlankSpaceAdded)  
            INSERT #CommentText VALUES  
            ( @LineId,  
              isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText, @BasePos, @AddOnLen), N''))  
            select @Line = NULL, @LineId = @LineId + 1,  
                   @BasePos = @BasePos + @AddOnLen, @BlankSpaceAdded = 0  
        end  
        select @Line    = isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText, @BasePos, @CurrentPos-@BasePos + @LFCR), N'')  
        select @BasePos = @CurrentPos+2  
        INSERT #CommentText VALUES( @LineId, @Line )  
        select @LineId = @LineId + 1  
        select @Line = NULL  
    end  

K, so this is how sp_helptext does it. Wrap the results are a table function returning rows for CRLF characters returned by the object_definition.

Answer 4

Since you're using SQL Server 2016, you can use STRING_SPLIT and a trick from Aaron Bertrand to split on carriage-return/line-feed.

DECLARE 
  @olddelim nvarchar(32) = char(13) + Char(10),
  @newdelim nchar(1)     = NCHAR(9999); -- pencil (✏)

SELECT * FROM STRING_SPLIT(REPLACE(OBJECT_DEFINITION(object_id('usp_radhe')), @olddelim, @newdelim), @newdelim);

Answer 5

If the goal is to get back the original code with the line breaks, you can just query from the source (sys.sql_modules) with the datagrid output configured to retain line breaks.

Tools>Options>Query Execution>SQL Server>Results to Grid

• Check Retail CR/LF on copy or save
• Maximum Characters Retrieved > Non XML data > (set as needed)

SELECT definition
FROM sys.sql_modules
WHERE OBJECT_NAME(object_id) = 'usp_radhe'

Answer 6

For MSSQL Server 2012 you can use a recursive function. E.g.:

/* Recursive function, to split an object (like stored procedure) into individual lines, with line number */
declare @delimiter nvarchar(2) = char(10);
declare @objectName sysname = 'dbo.MyObject'

;with CTE as (
  select 
    0 as linenr
  , object_definition( object_id(@objectName)) as def
  , convert(nvarchar(max), N'') as line
  union all
  select 
    linenr + 1
  , substring(def, charindex(@delimiter, def) + len(@delimiter), len(def) - (charindex(@delimiter, def)))
  , left(def, charindex(@delimiter, def)) as line
  from CTE
  where charindex(@delimiter, def) <> 0
 )
select linenr, line
from CTE
where linenr >= 1
OPTION (MAXRECURSION 0);