How to sort DDL by name of the object

Question

I want to sort the generated DDL of a database by name of the object. For example, I have the following DDL:

CREATE TABLE MyTable3 (
  col1 integer,
  col2 varchar(10)
);

CREATE TABLE MyTable1 (
  col1 integer
);

CREATE TABLE MyTable2 (
  col1 integer,
  col2 varchar(10),
  time timestamp
  time2 timestamp
);

And I want the following output:

CREATE TABLE MyTable1 (
  col1 integer
);

CREATE TABLE MyTable2 (
  col1 integer,
  col2 varchar(10),
  time timestamp
);

CREATE TABLE MyTable3 (
  col1 integer,
  col2 varchar(10)
);

I have tried with awk, but I do not know to specify multiple lines: awk -F; '{print $NB}'

I found that msort could be the solution, but this package is not longer maintained.

As you can see, each element can have different quantity of lines, and I want to sort them by the first line which contains the name of the object.

What other options do I have to sort a document by multiple lines and a specific terminator (semi-colon).

score 0 · Answer 1 · answered Jan 28 '20 at 07:25

Can you please try below gawk solution. The solution however assumes the tablename is in the same line as CREATE TABLE.

awk '
BEGIN {
}
{
        if($0 ~  /CREATE TABLE/) {
                found = match($0, /CREATE TABLE (.*) \(/, ary);
                if(found != 0)
                {
                        tablename=ary[1];
                }
                oneDDL=$0;
        }
        else if($0 ~  /;/) {
                oneDDL=oneDDL"\n"$0;
                arr[tablename]=oneDDL;
        }
        else {
                oneDDL=oneDDL"\n"$0;
        }
}
END {
        asort(arr);
        for(i in arr) {
                print arr[i];
                print "\n";
        }
}' Input_File

Put tablename in an array arr as index with value as the text of DDL oneDDL.

Sort the array. Then print the contents of the array.

How to sort DDL by name of the object

1 Answers1