In Sybase ASE, sp_helptext shows suceeded but nothing else is displayed

Question

I am using Sybase ASE for the first time, and assume I am missing some very basic things.

sp_helptext 'dbname.procedure_name'

returns

'dbname.procedure_name' succeeded.

but nothing else. A misspelling returns error.

How do I capture or display the results?

Thanks.

sp_version

returns

sp_version

I am using Oracle SQL developer with a plug in. Would using other gui yield better results? Is this a permission issue?

Answer 1

Some background:

1 - the sp_% procs are stored in the sybsystemprocs database but available for execution from within all databases

2 - the sp_help% procs work on the sys% tables in the 'local' database

Putting these tidbits together ... you need to execute sp_helptext from within the dbname database in order to obtain the text of procedure_name (the stored procedure's text is stored in the dbname..syscomments table).

A couple ways to accomplish this:

-----------------------------
-- option #1:
-- place yourself in the database

use dbname
go

-- now run sp_helptext

exec sp_helptext procedure_name
go
-----------------------------
-- option #2
-- preface the sp_helptext invocation with the name of the
-- database in which you wish to run sp_helptext

execute dbname..sp_helptext procedure_name
go
-----------------------------

Answer 2

Also, there's a newer sp_showtext command that shows the text without annoying line breaks every 255 characters.

Be careful with renamed objects. The sp_helptext/sp_showtext output will usually show the old object name.

Answer 3

SQL developer didn't display the results (probably didn't correct way to execute query). Using a different gui solved the problem.