4

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

  • 6
    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
2

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);
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.

2

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.

0

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);
0

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.

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.