Created stored procedure

[Former Member]

Hi all,

Referring to note 1801984, the script attached is to create a stored procedure sp_thresholdaction_logfreespace    
Is there any way I can verify that the stored procedure has indeed been created?

I tried sp_helptext <proc name>, sp_procxmode <proc name>, but to no avail.
Appreaciate if someone could advise me.

Thanks.

[former_member188958]

sp_helptext should produce output if the procedure was created in your current database.

For simply confirming the procedure was created, sp_help would also work.

You can also check by querying the system table sysobjects directly with

     select * from sysobjects where name = sp_thresholdaction_logfreespace

or if you are suspecting a possible typo in the name you can get a list of all

the procedures in the current database with

     select name from sysobjects where type = "P"

The script has a "use saptools" command in it before the procedure is created,

are you currently in the saptools database when you are running sp_helptext?

sp_helptext only looks in your current database.

Note that the "sp_" prefix for the object name has some "magic" to it.  ASE generally

only looks for objects in the current database (unless the name is fully qualified with database.owner.name rather than just owner.name or name). When you execute a

procedure that starts with "sp_", ASE first searches for the procedure in your current

database, if not found there it then searches for it in sybsystemprocs, if not found there

ASE searches for it in the master database.

Just to share some related knowledge, the most common reason for a procedure to

not get created from a script file is forgetting to have a "go" followed by a new line at

the end of the file. Without this batch terminator, the script doesn't get sent to ASE

and isql just exists. The "g" in the "go" also has to be the first character on it's line. 

None of this appears to be an issue with this script file, but I thought it worth mentioning.

-bret

[former_member188883]

Hi Castro,

Please try this command

select proc_name from sysprocedures where proc_defn like "<procedure name>"

You may also use sp_depends command as described in the link below

http://infocenter.sybase.com/archive/index.jsp?topic=/com.sybase.help.ase_15.0.sprocs/html/sprocs/sp...

Hope this helps.

Regards,

Deepak Kori