June 28, 2013 at 9:33 am
Currently I am using the following code to read all content of sp or fn and it works in most cases, however, it does return more than one rows if the content's size is too large, is there better to get this? Thank you.
SELECT text
FROM syscomments
WHERE id = (SELECT id FROM sysobjects WHERE name = 'spname')
ORDER BY colid
June 28, 2013 at 9:39 am
You can use
EXEC sp_helptext 'spname'
Or right-click on object explorer and choose "Modify" option
June 28, 2013 at 9:46 am
Thanks. EXEC sp_helptext returns row by row while I want only one row
June 28, 2013 at 9:48 am
halifaxdal (6/28/2013)
Currently I am using the following code to read all content of sp or fn and it works in most cases, however, it does return more than one rows if the content's size is too large, is there better to get this? Thank you.
SELECT textFROM syscomments
WHERE id = (SELECT id FROM sysobjects WHERE name = 'spname')
ORDER BY colid
the old syscomments returned rows of 4K slices of long procedures.
you can use one of the views:
select definition from sys.sql_modules where object_id = object_id('sp_procname')
you can also use a built in function:
SELECT OBJECT_DEFINITION('sp_procname')
Lowell
June 28, 2013 at 9:50 am
I end up using this:
declare @result nvarchar(max)
set @result = ''
select @result = @result + text from syscomments
WHERE id = (SELECT id FROM sysobjects WHERE name = 'spname')
ORDER BY colid desc
select @result
I am not sure if this is the best practice.
June 28, 2013 at 9:52 am
or grab them all in one data set, duh, i should have posted this first!
SELECT object_schema_name(object_id),
object_name(object_id),
DEFINITION
FROM sys.sql_modules
ORDER BY object_name(object_id)
Lowell
June 28, 2013 at 9:52 am
How about sys.sql_modules?
select definition
from sys.sql_modules
where object_id = object_id('<your stored proc name here>')
----------------------------------------------------------------------------
Sacramento SQL Server users group - http://sac.sqlpass.org
Follow me on Twitter - @SQLDCH
----------------------------------------------------------------------------
June 28, 2013 at 9:54 am
select definition from sys.sql_modules where object_id = object_id('sp_procname')
is better than mine.
Thank you
June 28, 2013 at 9:55 am
I agree with Lowell, you should try using sys.sql_modules instead of syscomments. Although, it seems easier to use the built-in function with a slight correction.
SELECT OBJECT_DEFINITION(OBJECT_ID('spname'))
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply