Getting the stored proc script instead of its resulting

Question

Post reply

Getting the stored proc script instead of its resulting

Luiz-458831

SSCommitted

Points: 1904
More actions
June 29, 2007 at 1:41 pm

#196593

Hi Guys,

Does somebody know if it is possible to get the content (script) of a stored procedure through a select statement?

I know we can list the stored procedures name using the table sysobjects

(SELECT Name FROM sysobjects WHERE XType='P'),

But can I get the procedure script ? I mean the actual content of the procedure

(BEGIN
            SET NOCOUNT ON

            DELETE          store_Products_ReportGroups
            WHERE           ReportGroupID = @ReportGroupID

            DELETE          store2_Products_ReportGroups
            WHERE           ReportGroupID = @ReportGroupID

            DELETE          healthc.tblReportGroups
            WHERE           ReportGroupID = @ReportGroupID
END)

Thanks a lot,
Luiz.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Mark Shvarts 1 SSCertifiable Points: 5702 More actions · Answer 1

select

object_definition(object_id('dbo.SampleProcedure'))

Luiz-458831 SSCommitted Points: 1904 More actions · Answer 2

Thanks Mark.

Unfortunately it seems that the function object_definition isn't part of MSSQL-2000 - the version that I am using.

But your answer led me to a MSSQL-2000 alternative: the column TEXT in table syscomments. A solution not as good as yours, but good enough for what I need.

Luiz.

jezemine SSCrazy Eights Points: 8059 More actions · Answer 3

beware that the DDL will be split across multiple rows in syscomments if the proc is more than 4000 chars.

---------------------------------------
elsasoft.org