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'))
Best Answer
For MSSQL Server 2012 you can use a recursive function. E.g.: