Generate DDL Tool
The Generate DDL option for RazorSQL allows users to generate DDL (data definition language) for database objects such as tables, views, and indexes. DDL is the SQL command used to create an object. For example, users can create database tables using the SQL Create Table command, create views using the SQL Create View command, and create indexes using the SQL Create Index command. The generate DDL tool recreates the Create Table, Create View, or Create Index command for a particular database object.
The DDL generator can be selected from multiple places inside RazorSQL. To generate DDL for a single object such as a table, the context menu of the database browser can be used. Table DDL is also displayed in the Describe tool. To generate DDL for an entire schema or database, the DB Tools -> Generate DDL menu option can be used. The entire schema / database option gives users the ability to generate table, view, and index DDL for all objects in a database or schema. Along with using the Export Tool to export data as SQL insert statements, users can export all tables, views, indexes, and data for a particular database using RazorSQL. This is useful for copying databases, or creating test databases locally, etc.
If selecting the single object option, RazorSQL will attempt to generate the database specific DDL by analyzing the selected object. For example, for tables, RazorSQL would look at column names, data types, sizes, primary keys, and foreign keys. The DDL generator may not be able to show all DDL for the particular object. For example, if there are check constraints or storage details on a table, RazorSQL may not display all of that information. RazorSQL does offer exact DDL generation for databases that support this option. For exact DDL generation, there is another option on the context menu of the database browser. For example, for MySQL, RazorSQL offers the Show Table option to get the exact MySQL table DDL. For Oracle, RazorSQL can call the DBMS_METADATA.GET_DDL system stored procedure. RazorSQL offers the exact DDL calls for MySQL, Oracle, SQLite, and Teradata.
RazorSQL can generate table DDL for all supported databases such as Access, DB2, Derby, Firebird, FrontBase, H2, HSQLDB, Informix, Ingres, InterBase, JavaDB, MySQL, OpenBase, Oracle, Pervasive, PostgreSQL, SQL Server, SQLite, Sybase, SQL Anywhere, Teradata, and more. RazorSQL can generate view DDL for most but not all databases. RazorSQL can generate index DDL for most but not all databases.
Listed below is an example of the Create Table DDL generated by RazorSQL. RazorSQL will generate the column names, data types, sizes, nullability, primary keys, and foreign keys for a table.
CREATE TABLE PUBLIC.EMPLOYEE (
SSN VARCHAR(25) NOT NULL,
FNAME VARCHAR(25),
MINIT CHAR(1),
LNAME VARCHAR(50),
ADDRESS VARCHAR(50),
SEX CHAR(1),
SALARY INTEGER,
SUPERSSN VARCHAR(50),
DNO INTEGER,
PRIMARY KEY (SSN)
);
ALTER TABLE PUBLIC.EMPLOYEE
ADD FOREIGN KEY (DNO)
REFERENCES DEPARTMENT (DNUMBER);
Listed below is an example of the Create Index DDL generated by RazorSQL. RazorSQL will typically be able to generate the uniqueness, and whether an index is ascending or descending along with the core index fields.
CREATE UNIQUE INDEX PUBLIC.IDX_DEPARTMENT ON PUBLIC.DEPARTMENT (DNUMBER)
