Switch to standard view 
  Sybase logo
 
 
 



Reverse Engineering MS SQL SERVER, SYBASE SQL SERVER 4.x, System 10, and System 11 Databases via a Script File

Technical Sheet

PowerDesigner, formerly known as S-Designor, allows you to Reverse Engineer an existing database via an ODBC connection to your DBMS or via a SQL script file. This document addresses how to Reverse Engineer a SQL SERVER database via a SQL script file. However, it is recommended to Reverse Engineer SQL SERVER type databases via an ODBC connection. For more information on this option, refer to the AppModeler or DataArchitect User's Guide.


Introduction The first step in the Reverse Engineering of a SQL SERVER database consists of producing a SQL script containing the database description. This script can then be reloaded with the Reverse Engineering command from the File menu in a PowerDesigner (S-Designor) PDM. Reverse Engineering allows the recuperation of the table, column and index definition as well as the regeneration of inter-table references. Finally, from this PDM, S-Designor generates the corresponding CDM.


The Reverse Engineering process shown is further explained below.


(A) Preparation
It is possible to obtain the table, index, primary and foreign key creation script from the SQL Server database.

The USBATCH tool, provided by PowerDesigner enables you to generate SQL commands for table, key and index creation from the SQL Server database environment.

An example of the use of the USBATCH tool:
      c:\sybase\bin\isql -U sdp_user -P sdp_pass -S sdp_server -o c:\temp\crebas.sql
use sdp_database
go
:r c:\pwrs\pd6\tools\usbatch
go
exit




Explanation of commands issued with the use of the USBATCH tool
TermOptionsDescription
SQL interpreterIsqlAn Isql editor. Supplied with your DBMS client software
User id -UOption to specify the user id to connect to your database
Password-POption to specify the password needed to connect to your database
Server name-SOption to specify the server name that your database resides on
Output fileoOption to specify the output file to contain redirected SQL script
Execute batch file:rCommand to run the usbatch file

NOTE:
The above options are case sensitive.

This procedure will work using WISQL. However, the output will be restricted to 64k due to an existing internal buffer limit within the WISQL application. This 64k limit is too small to hold the database schema generated by the USBATCH tool. As a result, it is recommended that this procedure be executed using ISQL.exe. ISQL.exe is a DOS application and must be executed from a DOS prompt.


Step 1: From a DOS prompt, enter the command line to run the SQL interpreter with the following
options -U <user name> -P <password> -S <server name> -o <output file> .

example: C:\xxx\isql -U <user name> -P <password> -S <server name> -o <output file>

Supplying the output file, redirects this command line to the file which will contain the SQL orders (preferably with the extension .sql). If this option is obmitted, the SQL output will be directed to your monitor screen.


Step 2: Enter "use" followed by the database name on which Reverse Engineering and hit "Return". Enter the terminator "go" and hit "Return" so that the access request can be interpreted.

Example: use <database name> <press Return/Enter>
go <press Return/Enter>


Step 3: enter ":r" and the location of the usbatch file and hit "Return". (This file can be found in the tools subdirectory of the S-Designor directory.) Enter the terminator "go" and hit "Return" to generate the SQL script and wait for the prompt.

Example: :r c:\pwrs\pd6\tools\usbatch <press Return/Enter>
go <press Return/Enter>

(Note: c:\pwrs\sd50\tools\usbatch will be the path if using version 5.x)

At the completion of the script generation, type "exit" and press "Return" to exit the command interpreter.

Example: exit <press Return/Enter>

The crebas.sql file has now been created in the specified directory or the current directory of the ISQL editor.

Example : The script file crebas.sql produced by the accessory USBATCH.

/*-----------------------------------------------------------*/
/* Tablename: training */
/*-----------------------------------------------------------*/
create table training
(
id_training char( 5) not null,
id_co char( 5) null,
length integer null,
maxi integer null,
level_req integer null,
title char( 30) null
)
go

sp_primarykey training, id_training
go

create unique index training_pk
on training (id_training )
go
create index training_fk1
on training (id_co )
go

/*-----------------------------------------------------------*/
/* Tablename: company */
/*-----------------------------------------------------------*/
create table company
(
id_co char( 5) not null,
name_co char( 30) null
)
go

sp_primarykey company, id_co
go

create unique index company_pk
on company (id_co )
go

/*----------------------------------------------*/
/* Foreign keys */
/*----------------------------------------------*/

sp_foreignkey training, company, id_co
go




(A) Script->PDM
Reverse Engineering an SQL Server database involves reversing the .sql script file to obtain a Physical Data Model:

Step 1: Start up PowerDesigner.

Step 2: For Reverse Engineering many possibilities exist:

a) If no model is open, choose the Reverse Engineering command from the File menu.

b) If a CDM is already open, choose the Physical Model option from the File menu, then choose the Reverse Engineering command from the Physical model sub-menu.

After completing "a" or "b", the Choose the target Database dialog box appears on the screen. Select "MS SQL Server 6.x", "SYBASE SQL Server 4.x" , "SYBASE System 10" or "SYBASE System 11" from the database list then click on "OK". The Choose the name of script file dialog box appears; after selecting or entering the .sql script file, click on "OK".


c) If a PDM is already open, create a new PDM (select the New command from the File menu). The Choose the target Database dialog box appears on the screen. Select "MS SQL Server 6.x", "SYBASE SQL Server 4.x", "SYBASE System 10" or "SYBASE System 11" from the database list then click on "OK". Next select the Reverse Engineering command from the File menu. When the Choose the name of script file dialog box appears on the screen select or enter the desired .sql file and click on "OK".

When Reverse Engineering is complete, a confirmation box appears asking, "Do you want to generate references between the tables?". Answer "Yes" to generate references.


Example: Reverse Engineering crebas.sql generates the following PDM:



The rules for generation of references in the PDM from the script file are as follows:

- S-Designor uses the ANSI SQL declarations "primary key" and "foreign key references" or in SQL Server, "sp_primarykey" and "sp_foreignkey" to explain the inter-table relations.
- If these indications are not present or are not supported by the database used, S-Designor uses unique indexes to infer primary keys; then, based on the column name identities, it infers the foreign keys. The references are therefore named by S-Designor : "ref_n" where "n" is a number. Double-click on the reference to rename it. This name will be placed at the association level after Reverse Engineering (see step [C] PDM -> CDM).




(C) PDM->CDM To generate the Conceptual Data Model from the Physical Data Model:

Step 1: After the PDM is loaded, select the Generate Conceptual Model command in the Dictionary menu.
A confirmation box appears on the screen stating, "This command will generate a Conceptual Data Model from the current Physical Model. Do you want to continue?". Answer "Yes" to generate the CDM.



Example: CDM generation from the PDM:

The rules for generation of a CDM from a PDM are as follows:
- A table becomes an entity.
- A column which is not a foreign key becomes an information and a property.
- Each reference becomes an association 0,n - 0,1.
- A table possessing primary key columns which are both primary and foreign keys, becomes an association 0,n - 0,n.
- The primary key of a table becoming an entity becomes the identifier of this entity.






Back to Top
© Copyright 2010, Sybase Inc.