Hi all,

This came up today as a support question, so I thought I'd share it with
everyone.  The attached procedure shows how to copy entire tables, including
data, indexes, and triggers, using the new system information available in
2.12 and higher.

Enjoy !

Tim Young
Elevate Software
www.elevatesoft.com

==================================

CREATE PROCEDURE CopyTable(IN SourceTableName VARCHAR, IN DestTableName
VARCHAR, IN IncludeData BOOLEAN)
BEGIN
  DECLARE InfoCursor CURSOR FOR InfoStmt;
  DECLARE SQL VARCHAR;

  SET STATUS MESSAGE TO 'Creating table
'+QUOTEDSTR(DestTableName,'"')+'...';

  PREPARE InfoStmt FROM 'SELECT CreateSQL INTO ? FROM Information.Tables
WHERE Name=?';
  OPEN InfoCursor USING SQL,SourceTableName;

  IF SQL IS NOT NULL THEN
     SET SQL = REPLACE(QUOTEDSTR(SourceTableName,'"'),
QUOTEDSTR(DestTableName,'"'), SQL);
     EXECUTE IMMEDIATE SQL;
  END IF;

  IF IncludeData THEN
     SET STATUS MESSAGE TO 'Copying rows for table
'+QUOTEDSTR(DestTableName,'"')+'...';
     SET SQL = 'INSERT INTO '+QUOTEDSTR(DestTableName,'"')+' SELECT * FROM
'+QUOTEDSTR(SourceTableName,'"');
     EXECUTE IMMEDIATE SQL;
  END IF;

  PREPARE InfoStmt FROM 'SELECT * FROM Information.Indexes WHERE
TableName=? AND '+
                        'Type IN (''Index'',''Text Index'')';
  OPEN InfoCursor USING SourceTableName;
  FETCH FIRST FROM InfoCursor ('CreateSQL') INTO SQL;

  SET STATUS MESSAGE TO 'Creating indexes for table
'+QUOTEDSTR(DestTableName,'"')+'...';

  WHILE NOT EOF(InfoCursor) DO
     SET SQL = REPLACE(QUOTEDSTR(SourceTableName,'"'),
QUOTEDSTR(DestTableName,'"'), SQL);
     EXECUTE IMMEDIATE SQL;
     FETCH NEXT FROM InfoCursor ('CreateSQL') INTO SQL;
  END WHILE;

  PREPARE InfoStmt FROM 'SELECT * FROM Information.Triggers WHERE
TableName=?';
  OPEN InfoCursor USING SourceTableName;
  FETCH FIRST FROM InfoCursor ('CreateSQL') INTO SQL;

  SET STATUS MESSAGE TO 'Creating triggers for table
'+QUOTEDSTR(DestTableName,'"')+'...';

  WHILE NOT EOF(InfoCursor) DO
     SET SQL = REPLACE(QUOTEDSTR(SourceTableName,'"'),
QUOTEDSTR(DestTableName,'"'), SQL);
     EXECUTE IMMEDIATE SQL;
     FETCH NEXT FROM InfoCursor ('CreateSQL') INTO SQL;
  END WHILE;
END

==================================

Excellent Tim, thanks. I'll have to download 2.12 now