Extract Sp and Ddl Scripts in Sybase Server

Extract SP and DDL Scripts in sybase Server

Look into the ddlgen utility included with sybase, ususally found in the Sybase Central directory, or the $SYBASE/ASEP/bin. This should be able to generate scripts to create all of the database objects including user-defined datatypes (UDD), indexes, stored procedures, etc.

To extract DDL statements of a Sybase database

I believe you can retrieve some of the ddl from syscomments and sysobjects.

From the documentations:

syscomments contains entries for each view, rule, default, trigger,
table constraint, partition, procedure, computed column,
function-based index key, and other forms of compiled objects. The
text column contains the original definition statements. If the text
column is longer than 255 bytes, the entries span rows. Each object
can occupy as many as 65,025 rows

SELECT so.name, sc.text
  FROM syscomments sc, sysobjects so
 WHERE sc.id = so.id
  AND sc.texttype = 1   --1 for user defined objects/0 for system objects
 ORDER BY so.name

Obviously this isn't quite complete, but it should get you pretty close. Alternatively, you would need your java program to call the command line ddlgen utility.

I don't have an ASE install at the moment, so I apologize if the code isn't quite right.

Sybase: Generate a script Between two SQL?

If you don't have one, you can create a model from your initial database, with File > Reverse Engineer > Database.

Create a copy of this model with File > Save As (as type Archived PDM), say copy.apm.

Then modify the model as you wish.

Then use Database > Apply Model Changes to Database, using the option Using an archive model (select the above copy.apm), to generate the ALTER script.

How do I extract Sybase (12.5) table DDL via SQL?

IIRC there's a tool called DBSchema ( peppler.org/downloads/dbschema-2_4_2.zip is the best URL I was able to find ) - in case the URL doesn't ring any bells, Mike Peppller is the author of sybperl. You can likely reverse engineer the code for that script if you prefer to roll your own.

As far as SQL-wise, the table info is in sysobjects table and the column info is in syscolumns in Sybase.

You can also use stored procs: http://www.razorsql.com/articles/sybase_admin_queries.html

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

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)

Read stored procedure source code from Sybase ASE 15.7 database

Vince's hint was to use the stored procedure sp_helptext. This stored procedure produces more than one resultset and caused me difficulties.

RobV's answer was to use the stored procedure sp_showtext. This seems the better solution, cause it returns only one resultset. But I tried it with jdbc and it ever throws in a sql exception ("must return a resultset").

After my fails I find another solution, which need against the other two answers only a execution of a select statement. This works for me! Here is the code:

    StringBuffer sbSP = new StringBuffer();
    String query = "select c.text "
                  +"from syscomments c, sysobjects o "
                    +"where o.id=c.id and o.name='"+spName+"' order by c.colid";

    PreparedStatement stmt = connection.prepareStatement(query);
    ResultSet rs = stmt.executeQuery();
    while (rs.next()) {
        //loop over every row of the stored procedure
        sbSP.append(rs.getString("text") + "\n");
    }

Export multiple stored procedures to text files

Use the Generate Scripts tool in SSMS:

1. Right Click Database in Object Explorer.
2. Tasks -> Generate Scripts.
3. If given the "tutorial" click Next.
4. Select "Select specific database objects" and tick "Stored Procedures". Click Next.
5. Choose export method. Likely here you want "Save as script file" with "one script file per object" selected. Ensure you choose the export location.
6. Click Next and Finish buttons as required.

---

Related Topics