How to do 'Generate DDL' in Sybase Central in isql on the command line?

Question

In Sybase Central when I right click on a stored procedure and choose 'Generate DDL', then I see the definition of the stored procedure, but also the grants for example.

How do you do that on the command line with isql?

Answer 1

Best/recommended approach would be the ddlgen utility program; this will generate the DDL for the proc's text, sp_procxmode settings, and permissions. ddlgen is the 'go to' tool for reverse engineering Sybase ASE DDL.

Sample run:

$ ddlgen -SASE400 -Ppassword -Usa -TP -Nsybsystemprocs.dbo.sp_helptext

-----------------------------------------------------------------------------
-- DDL for Stored Procedure 'sybsystemprocs.dbo.sp_helptext'
-----------------------------------------------------------------------------

print '<<<<< CREATING Stored Procedure - "sybsystemprocs.dbo.sp_helptext" >>>>>'
go

use sybsystemprocs
go

IF EXISTS (SELECT 1 FROM sysobjects o, sysusers u WHERE o.uid=u.uid AND o.name = 'sp_helptext' AND u.name = 'dbo' AND o.type = 'P')
BEGIN
        setuser 'dbo'
        drop procedure sp_helptext

END
go

IF (@@error != 0)
BEGIN
        PRINT 'Error dropping Stored Procedure sybsystemprocs.dbo.sp_helptext'
        SELECT syb_quit()
END
go

setuser 'dbo'
go


/*
** sp_helptext
**

... snip ...

*/
create or replace procedure sp_helptext(
                  @objname              varchar(325)    = NULL
                , @grouping_num         int             = NULL
                , @numlines             int             = NULL
                , @printopts            varchar(256)    = NULL
                , @trace                int             = 0
) as

... snip ...

return (0)
end

go

Grant Execute on dbo.sp_helptext to public Granted by dbo
go

sp_procxmode 'sp_helptext', anymode
go

setuser
go

Other options would include:

• defncopy utility program for the text of the proc
• sp_helptext for text of the proc
• sp_helprotect for permissions (though you will need to parse the output to generate the necessary grant/revoke commands)