7

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'

enter image description here

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'))

enter image description here

5
  • 8
    Note that this is exclusively a presentation issue of Management Studio. OBJECT_DEFINITION gives you back the exact string, including newline characters, but SSMS simply replaces those with spaces when it displays it in grid mode (to keep rows together). If you choose "Results to Text" (Ctrl-T) it will print the definition including newlines, but it will almost certainly be cut off, due to another limitation. Scott's answer in text mode will give you the definition as-is, presentation-wise, but OBJECT_DEFINITION is already "correct". – Jeroen Mostert Nov 20 '17 at 13:14
  • @JeroenMostert and Marcello: please see the Update section of my answer as it pertains to a more recent development related to the "Results to Text" option :-) – Solomon Rutzky Mar 10 at 19:10
  • Marcello: it just occurred to me that you never actually state in the question what your specific requirements are. How exactly are you going to use the output? Does it require line numbers along with the breaks, or just line breaks? Can you please update the question to clarify precisely what you're looking for? Thanks :) – Solomon Rutzky Mar 11 at 13:48
  • @SolomonRutzky I liked the simplicity of this answer and it works best in most environments. However, it is a variation of your answer here that I use mostly. Just line breaks a function that replaces sp_helptext basically. – Marcello Miorelli Mar 11 at 14:28
  • @SolomonRutzky it is your procedure here that I use with a few teaks and I am most thankful to you for that! – Marcello Miorelli Mar 11 at 14:30
1

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);
1
  • this has worked for me best! other answers also worked but this one is so simple. I have not tested it against very large object_definitions though! – Marcello Miorelli Mar 11 at 11:56
5

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.

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.

1
3

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);
1
3

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 &lt;, &gt;, &amp;, and &quot;, 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.

12
  • Very nice @Solomon. Thank you. I was wondering if you could suggest a way to return the string from the XML as NVARCHAR(MAX)? – SteveC Mar 8 at 17:55
  • I'm thinking now to return the string as XML to C# and not deal with this conversion. The purpose of converting to NV(MAX) was so the string could be returned to C# as JSON. Using XML seems preferrable in this case – SteveC Mar 8 at 20:29
  • Xml worked. Ha, thanks again :) – SteveC Mar 8 at 21:24
  • 1
    Hi @SteveC . Yer welcome, glad it worked for you. However, if you are not viewing it in SSMS, then why bother with any of this in the first place? Simply getting the return value from OBJECT_DEFINITION(OBJECT_ID(N'schema_name.object_name')) should be sufficient. Meaning, converting that output to XML is most likely an unnecessary step for your purposes; the only reason this method is being used in this context is to preserve formatting while viewing in SSMS. – Solomon Rutzky Mar 8 at 23:12
  • It's for codegen which needs the formatting too. Returning as string would then require fixing the format. Your answer helped me move forward. Thanks again – SteveC Mar 9 at 12:23
1

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'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.