Exporting DDL objects from the database (to one file and to several files)

EXPORTING ALL DDL OBJECTS INTO A SINGLE SCRIPT

If you need to export ddl of objects from your source Sybase ASE database into a single script, you can use the ddlgen utility.

This utility is part of the Sybase ASE installation and allows you to generate DDL scripts for various database objects, such as tables, views, procedures, triggers, and more. Please follow these steps:

1. Open a Command Prompt (cmd) and navigate to the directory where Sybase ASE is installed.
2. Run the following command to export all DDL objects:

ddlgen -U[username] -P[password] -S[server]:[port] -N[dbname] > all_ddl.sql

where:

• -U[username] specifies your database login username.
  
• -P[password] specifies your database password.
  
• -S[server]:[port] specifies the Sybase ASE server and port (replace [server]:[port] with your actual server and port details).
  
• -N[dbname] specifies the name of the database from which you wish to generate the DDL scripts.
  
• > all_ddl.sql directs the output to a file named all_ddl.sql.
  

This command will create a single script file, all_ddl.sql, containing the DDL scripts for all objects in the specified database.

You can find more detailed documentation on the ddlgen command here.

BREAKING DDL OBJECTS INTO SEPARATE FILES

If you wish to break down the exported DDL scripts into separate files for each object, you can use the following steps after generating the all_ddl.sql file:

Use PowerShell to parse the all_ddl.sql file and separate the DDL scripts into individual files (before doing this, make sure you are in the directory where the all_ddl.sql file is created, if not, use the command cd C:\file_path). For your convenience, we have provided a PowerShell script below that will create separate files for each object type (e.g., Tables, Procedures, Functions, Triggers, Views, User Defined Types):

# Reading the contents of the file
$content = Get-Content all_ddl.sql
$filename = $null
$objectType = $null

# Create directories for storing files
$baseDir = "DDL_Objects"
$categories = @("Tables", "Procedures", "Functions", "Triggers", "Views", "UserDefinedDatatype")
foreach ($category in $categories) {
    New-Item -ItemType Directory -Path "$baseDir\$category" -ErrorAction SilentlyContinue
}

foreach ($line in $content) {
    # Define object type
    if ($line -match "^CREATE\s+(TABLE|PROCEDURE|FUNCTION|TRIGGER|VIEW|TYPE)\s+(\w+)") {
        $objectType = $matches[1]   
        $name = $matches[2] -replace "[^a-zA-Z0-9_]", ""
        $subdir = switch ($objectType) {
            "TABLE"     { "Tables" }
            "PROCEDURE" { "Procedures" }
            "FUNCTION"  { "Functions" }
            "TRIGGER"   { "Triggers" }
            "VIEW"      { "Views" }
            "TYPE"      { "UserDefinedDatatype" }
            default     { "Other" }
        }
        $filename = "$baseDir\$subdir\$name.sql"
    }

    # Detection sp_addtype (UDT)
    if ($line -match "exec\s+sp_addtype\s+'(\w+)'\s*,") {
        $name = $matches[1] -replace "[^a-zA-Z0-9_]", ""  
        $filename = "$baseDir\UserDefinedDatatype\$name.sql"
    }

    if ($filename) {
        Add-Content -Path $filename -Value $line
    }
}

This script will separate the DDL content into folders such as Tables, Procedures, Functions, Triggers, Views, and UserDefinedDatatypes, creating individual files for each object. The screenshot provides an example of such a breakdown:

Once the DDL scripts are separated into individual files, you can use them for further processing or for importing into other systems.

If you have any additional questions, please feel free to contact us: support@ispirer.com