如何格式化 OBJECT_DEFINITION 的輸出，使其看起來與 sp_helptext 相同？

object_definition和sp_helptext都返回儲存過程的原始碼 - 但是我如何使用object_definition和獲取原始碼，包括目前的換行符sp_helptext？

例如在下面的程式碼中，我創建了一個儲存過程：

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 

使用sp_helptext我可以很好地查看原始碼：

sp_helptext 'usp_radhe'

使用object_definition我在一行中獲得了原始碼，這對我不利：

select OBJECT_DEFINITION(object_id('usp_radhe'))

對於 MSSQL Server 2012，您可以使用遞歸函式。例如：

/* 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);

引用自：https://dba.stackexchange.com/questions/191311