Sybase IQ - how to show stored procedure without wrapping text?

Question

Using Sybase IQ v12.7. Executing sp_helptext ProcedureName shows the text of the stored procedure which is fine. However it wraps lines at 80 characters. The question is, how to show text text of a stored procedure without wrapping?

In Sybase Central Java Edition there is a feature see the text of a stored procedure (Tranact-SQL) without line wrapping. So the question could be restated in a different way: how does Sybase Central get the text of a stored procedure without wrapping the text at 80 characters?

Basically looking for a programmatic way to dump out stored procedures for diff-ing and version control.

Thanks to any responses!

Shana

Answer 1

sp_helptext does not do any wrapping - it breaks up the procedure definition text at the newline characters and returns each line as a row. If you want the whole thing returned as a single string, you can select the procedure definition directly from the system table:

select proc_defn from SYS.SYSPROCEDURE where proc_name='<procedurename>'

Fenn

Excellent, thank you. This is exactly what I was looking for. When running sp_helptext in a client like sqldbx long lines in the stored procedure get wrapped at 80 chars. So perhaps that is an issue with the client or some setting on the client connection. In any case querying the SYS.SYSPROCEDURE table directly does the job. – Shana

Answer 2

Currently I have not access to sybase IQ or ASA server, but I think you are looking something like ASE sp__helptext from http://www.edbarlow.com/gem/procs_only/

Try to rewrite this one for ASA server.

Update:

Look at defncopy utility.

Georgetta

Answer 3

If you are using isql to execute the sp_helptext, then probbably you did not supply the "-w" parameter which sets column_width for current session. Try to do the same with isql -w999 to see if it still wraps.

For my own purposes, to compare stored procedures, I use the defncopy utility provided by Sybase, that kolchanov already mentioned.

Trey

Answer 4

If you don't have defncopy (either from Sybase or from FreeTDS) you can use this sqsh and sed-based script: https://gist.github.com/vjt/5920790

Baffle