How do I format the output from OBJECT_DEFINITION so that it looks the same as sp_helptext?
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.
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.
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);