Alter Table Script

Toad for Oracle & DevOps Toolkit
1

Hi everyone,

I would like to create an app in Toad that generates an "ALTER TABLE" script for existing tables across different schemas. Can I do this using the Automation Designer? And if so, how?

My approach would be to generate individual scripts within a loop using the "Compare Schemas" action and then write those scripts to a single file. Something like this:

image
image378×107 3.29 KB

Or is there an easier way?

Thanks

2

Hi WaHe,

Yes, you can do this in automation designer. I think the only thing you need is compare schemas, but I'm not 100% sure what you are trying to achieve.

If you want to compare tables from one source schema to multiple target schemas, set up schema compare like this:

image
image809×540 14.1 KB

If you want to compare tables in schemas A, B, C in one database to schemas A, B, C in another database, you can set up schema compare like this (uncheck "Match schemas automatically" if you want to choose the source and target pairs yourself):

image
image805×718 22.8 KB

In both of these cases, set up the output page like this (you can also check the other "results" boxes if you want)

image
image838×884 29.4 KB

Toad will create separate scripts for each comparison, but also a "MasterScript.SQL" which calls all of the other scripts. So it's not one big script, but it's only one script that you need to call.

Let me know if this solves your problem.

-John

3

Thanks for the quick reply. Is this a new feature? It’s the first time I’ve seen that you can compare multiple schemas. Cool. Maybe this is what I was looking for. But how do I set the filter for more then one schema?

image

4

Hi Waldemar,

Compare multiple schemas has been there for several versions, but it's easy to miss.

That filter does support multiple like A%;B%

Here, I have set it for T%;J%;B%.

Edit: I just found a bug here. If the schema filter is already set when you choose a target connection, the filter gets incorrectly used in your SQL (without breaking it up by the semicolons). Please set the filter AFTER choosing your target schema. I will fix this for next beta.

image
image591×741 24.8 KB

When "Match Schemas automatically" is unchecked, then the checked schemas in the grid is what will be compared. So I could uncheck some of them if I want and they would be left out of the comparison.

Also, If I want, I can manually choose a target schema (maybe it doesn't have the same name as source), and then check the "Include" box in that row.

Then Toad will compare whatever is checked in the grid.

When "Match Schemas automatically" is checked, then Toad will check the filter at runtime and find schemas in both databases with matching names to compare.

If you click Next a few times you will see a list of the schemas that will be compared as it's getting started.

image
image916×566 22.9 KB

Note: If you close and re-open schema compare, then the previous compare gets reloaded. In this case, the non-compared schemas are not present in the grid.

image
image919×619 12.2 KB

I really need a "refresh" button in there so you can re-add the the non-compared schemas more easily, but you can re-add them by changing to a different target connection, and then changing it back to the one you want.

-John

5

Thanks John, this is the solution for me. Sometimes you have to ask a pro. :sweat_smile:

Edit: Is there a way to write all sync scripts into a single file?

6

Sorry, there is no way to get Toad to create a single script.

You can, however use this powershell script to make a combined script using the MasterScript.sql

If you save it as CombineScripts.ps1, in the same folder, then you could call it from powershell like this:

.\CombineScripts.ps1 .\MasterScript.sql .\CombinedScript.sql

param (
    [Parameter(Mandatory = $true)]
    [string]$InputFile,

    [Parameter(Mandatory = $true)]
    [string]$OutputFile
)

$inputPath = (Resolve-Path $InputFile).Path
$baseDir = Split-Path $inputPath

$output = [System.Text.StringBuilder]::new()

foreach ($line in Get-Content $inputPath) {

    # Look for:
    # @.\some-directory\SyncScript.sql
    if ($line -match '^\s*@(.+\\SyncScript\.sql)\s*$') {

        $scriptPath = Join-Path $baseDir $Matches[1]
        $scriptPath = [System.IO.Path]::GetFullPath($scriptPath)

        if (-not (Test-Path $scriptPath)) {
            throw "Referenced script not found: $scriptPath"
        }

        [void]$output.AppendLine("-- BEGIN: $($Matches[1])")
        [void]$output.AppendLine((Get-Content $scriptPath -Raw))
        [void]$output.AppendLine("-- END: $($Matches[1])")
    }
    else {
        [void]$output.AppendLine($line)
    }
}

[System.IO.File]::WriteAllText(
    $OutputFile,
    $output.ToString(),
    [System.Text.Encoding]::UTF8
)
7

OK, then I'll have to take a detour.

Thanks, John.