Create date of store procedure

[DEBUINFO4GL]

How I find out the create date of particular store procedure in database?

 

[vpshriyan]

Hi Debu,

Database server does not store the date on which a stored procedure has been registered or compiled. It is strongly recommended to use the DOCUMENT keyword available in the SPL to store all documentation related information specific to a procedure along with creation and subsequent modified dates. The SQL below lists document contents of your stored procedure, if any:

select procname, seqno, data
from sysprocedures p, sysprocbody b
where p.procid=b.procid and datakey='D'
order by procname, seqno ;

A Stored Procedure is parsed & optimized when compiled, and an execution plan is stored in binary format in one of the catalog table. Sysprocplan stores these information along with plan created date. The plan gets updated if underlying objects has been changed or a DBA issues an UPDATE STATISTICS FOR PROCEDURE statement. Assuming that you have not updated the statistics, and no changes have been taken place in connected objects, the SQL below will give you the date of creation of the procedure or plan updated date whichever is very recent.

select unique p.procname, created
from sysprocedures p, sysprocplan l
where p.procid=l.procid ;

Regards,
Shriyan
"When you dial a wrong number, you never get a busy signal."