SAP Sybase ASE 15.7 database schema reverse engineering SQL using ddlgen example




Simplest example use of ddlgen for reverse engineering T-SQL schema


[sybase@centosdev bin]$ pwd
/home/sybase/SYB157/ASE-15_0/bin

./ddlgen -Usa -Psapassword -SSYBSERVER -TDB -Nraw_test > ddlgen.out
The database extracted was "raw_test".

The above is the command line to extract the T-SQL for:

database creation
tables
stored procedures
users

...and everything else!


Example of the top of the output file:

------------------------------------------- CODE BELOW CREATED BY ddlgen ----
USE master
go


PRINT "<<<< CREATE DATABASE raw_test>>>>"
go


IF EXISTS (SELECT 1 FROM master.dbo.sysdatabases
           WHERE name = 'raw_test')
        DROP DATABASE raw_test
go


IF (@@error != 0)
BEGIN
        PRINT "Error dropping database 'raw_test'"
        SELECT syb_quit()
END
go


CREATE DATABASE raw_test
            ON RAW_DATA001 = '500M' -- 128000 pages
        LOG ON RAW_LOG001 = '500M' -- 128000 pages
WITH DURABILITY = FULL
go


use raw_test
go

exec sp_changedbowner 'sa', true
go

exec master.dbo.sp_dboption raw_test, 'select into/bulkcopy/pllsort', true
go

exec master.dbo.sp_dboption raw_test, 'trunc log on chkpt', true
go

checkpoint
go
------------------------------------------- CODE ABOVE CREATED BY ddlgen ----


Extracting DDL for a single table


./ddlgen -Usa -Psapassword -SSYBSERVER -TU -Ncustomers -Draw_test > ddlgen.out

where
customers is a table
raw_test is a database
------------------------------------------- CODE BELOW CREATED BY ddlgen ----

-----------------------------------------------------------------------------
-- DDL for Table 'raw_test.dbo.customers'
-----------------------------------------------------------------------------
print '<<<<< CREATING Table - "raw_test.dbo.customers" >>>>>'
go

use raw_test
go

setuser 'dbo'
go

IF EXISTS (SELECT 1 FROM sysobjects o, sysusers u WHERE o.uid=u.uid AND o.name = 'customers' AND u.name = 'dbo' AND o.type = 'U')
        drop table customers

IF (@@error != 0)
BEGIN
        PRINT 'Error CREATING table "raw_test.dbo.customers"'
        SELECT syb_quit()
END
go

create table customers (
        first_name                      varchar(20)                      not null,
        last_name                       varchar(20)                      not null,
        signs_qty                       int                              not null,
        flag                            char(1)                              null,
        flags2                          char(1)                          not null
)
lock allpages
with dml_logging = full
 on 'default'
go


setuser
go

-----------------------------------------------------------------------------
-- DDL for Index 'inames'
-----------------------------------------------------------------------------

print '<<<<< CREATING Index - "inames" >>>>>'
go

create nonclustered index inames
on raw_test.dbo.customers(last_name)
go


-----------------------------------------------------------------------------
-- DDL for Index 'i2names'
-----------------------------------------------------------------------------

print '<<<<< CREATING Index - "i2names" >>>>>'
go

create nonclustered index i2names
on raw_test.dbo.customers(last_name, first_name)
go

------------------------------------------- CODE ABOVE CREATED BY ddlgen ----


Extracting DDL for a single stored procedure


ddlgen -Usa -Psapassword -SSYBSERVER -TP -Nraw_test.dbo.sp_altest

where
raw_test is the database
sp_altest is the stored procedure

------------------------------------------- CODE BELOW CREATED BY ddlgen ----

-----------------------------------------------------------------------------
-- DDL for Stored Procedure 'raw_test.dbo.sp_altest'
-----------------------------------------------------------------------------

print '<<<<< CREATING Stored Procedure - "raw_test.dbo.sp_altest" >>>>>'
go

use raw_test
go

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

END
go

IF (@@error != 0)
BEGIN
        PRINT 'Error CREATING Stored Procedure raw_test.dbo.sp_altest'
        SELECT syb_quit()
END
go

setuser 'dbo'
go

create proc sp_altest as
select count(*) from junk
select count(*) from junk

go


sp_procxmode 'sp_altest', unchained
go

setuser
go

------------------------------------------- CODE ABOVE CREATED BY ddlgen ----


It does appear that there is a bug in the error message code above, as the test comes before the procedure creation.

It is probably meant to say error dropping the procedure.

Conclusion


Extracting the schema with this tool is very straightforward if extracting for the whole database.

Multiple options and switches exist for more specific object extraction eg. just one stored procedure.


Disclaimer



No liability can be accepted for any loss due to any information found on this page.





Push Symbol. Push Symbol. Door logos



Alastair Cook email:
cookalastair @ btinternet.com

Page created - 20th Nov 2016


Push Pull Door