Hello,

Is there any way to duplicate a table with indexes,primary
key,constraits,column property ?

i need duplicate a table from two different Databases ( but only one
confuguration ) , in two different directory.

Thank you

Mauro


Two approaches:

1. Look at CREATE TABLE LIKE
2. Use reverse engineer and grab the appropriate bit of code

Roy Lambert [Team Elevate]

> 1. Look at CREATE TABLE LIKE

EDB 2.01 support CREATE TABLE LIKE ?
I don't found any information in EDB PDF Manual.


> 2. Use reverse engineer and grab the appropriate bit of code

Any more info ?

Mauro


1.

From the edb2sql.pdf

5.30 CREATE TABLE
Creates a new table.
Syntax
CREATE [TEMPORARY] TABLE <Name>
(
<ColumnDefinition>|LIKE <LikeDefinition>|<ConstraintDefinition>
[,<ColumnDefinition>|LIKE <LikeDefinition>|<ConstraintDefinition>]


Or type CREATE TABLE into the OLH in EDBManager

2. In EDBManager select a database and from Tasks select Reverse-Engineer Database


Roy Lambert [Team Elevate]

> 2. In EDBManager select a database and from Tasks select Reverse-Engineer
> Database

i can't use EDBManager.

Is there a function of EDB for to have at runtime the code for create the
new table from original table ?

Mauro


>i can't use EDBManager.

That has me fascinated - why not?

>Is there a function of EDB for to have at runtime the code for create the
>new table from original table ?

Only CREATE TABLE LIKE. Anything else you'd have to interrogate the system tables and build the appropriate sql yourself.

Roy Lambert

Mauro,

>>Is there any way to duplicate a table with indexes,primary
>>key,constraits,column property ?

You could use the result of this function in a TEDBScript, do a Script.ConvertSQL, and run
it in the database you like. After that, you can copy the contents if you want with a
"insert into database2.table2 select * from database1.table1".

(Credits to Tim, I only extracted it and twisted the formatting)

------------------------------

function getCreateTableSQL(TableName: String; DatabaseName: String='database';
SessionName: String='session'): String;
const nl: String = #13#10;
var qu1, qu2: TEDBQuery;
   con, col, str: String;
begin
  qu1 := TEDBQuery.Create(nil);
  qu1.SessionName  := SessionName;
  qu1.DatabaseName := DatabaseName;

  qu2 := TEDBQuery.Create(nil);
  qu2.SessionName  := SessionName;
  qu2.DatabaseName := DatabaseName;

  con := '';

  qu1.SQL.Text := 'select * from Information.Constraints where
TableName='+Engine.QuotedSQLStr(TableName);
  qu1.Open;
  while not qu1.Eof do begin
     con := con + 'CONSTRAINT "' + qu1.FieldByName('Name').AsString+'" ' +
qu1.FieldByName('Type').AsString;

     if (qu1.FieldByName('Type').AsString='Primary Key')
     or (qu1.FieldByName('Type').AsString='Foreign Key')
     or (qu1.FieldByName('Type').AsString='Unique')
     then begin
        str := '';
        qu2.SQL.Text:='select * from Information.ConstraintColumns '+
                      'where TableName='+Engine.QuotedSQLStr(TableName)+' AND '+
                     
'ConstraintName='+Engine.QuotedSQLStr(qu1.FieldByName('Name').AsString);
        qu2.Open;
        while not qu2.eof do begin
           if (str='')
           then str := str + '("'+qu2.FieldByName('ColumnName').AsString+'"'
           else str := str + ', "'+qu2.FieldByName('ColumnName').AsString+'"';
           qu2.Next;
        end;
        str := str + ')';
        qu2.Close;

        con := con +' '+str;
     end;

     if (qu1.FieldByName('Type').AsString='Foreign Key') then begin
        str := '';
        qu2.SQL.Text:='select * from Information.ConstraintColumns '+
                      'where
TableName='+Engine.QuotedSQLStr(qu1.FieldByName('TargetTable').AsString)+' AND '+
                     
'ConstraintName='+Engine.QuotedSQLStr(qu1.FieldByName('TargetTableConstraint').AsString);
        qu2.Open;
        while not qu2.eof do begin
           if (str='')
           then str := str + '("'+qu2.FieldByName('ColumnName').AsString+'"'
           else str := str + ', "'+qu2.FieldByName('ColumnName').AsString+'"';
           qu2.Next;
        end;
        str := str + ')';
        qu2.Close;

        con:=con+' REFERENCES "'+qu1.FieldByName('TargetTable').AsString+'" '+str;
     end;


     if (qu1.FieldByName('Type').AsString='Check')
     then con := con+' '+qu1.FieldByName('CheckExpr').AsString;

     if (not qu1.FieldByName('Description').IsNull)
     then con := con+' DESCRIPTION '+quotedstr(qu1.FieldByName('Description').AsString);

     qu1.Next;

     if not qu1.Eof
     then con := con + ',' + nl;

  end;
  qu1.Close;

  col := '';

  qu1.SQL.Text:='select * from Information.TableColumns where
TableName='+Engine.QuotedSQLStr(TableName);
  qu1.Open;

  while not qu1.Eof do begin

     col := col + '"'+qu1.FieldByName('Name').AsString + '"';

     if (qu1.FieldByName('Type').AsString='Char')
     or (qu1.FieldByName('Type').AsString='VarChar')
     or (qu1.FieldByName('Type').AsString='Byte')
     or (qu1.FieldByName('Type').AsString='VarByte')
     then begin
        col := col + ' ' + qu1.FieldByName('Type').AsString +
'('+qu1.FieldByName('Length').AsString+')';
        if (qu1.FieldByName('Type').AsString='Char')
        or (qu1.FieldByName('Type').AsString='VarChar')
        then col := col + ' COLLATE "'+qu1.FieldByName('Collation').AsString+'"';
     end
     else
     if (qu1.FieldByName('Type').AsString='BLOB')
     or (qu1.FieldByName('Type').AsString='CLOB')
     then begin
        col := col + ' ' + qu1.FieldByName('Type').AsString;
        if (qu1.FieldByName('Type').AsString='CLOB')
        then col := col + ' COLLATE "'+qu1.FieldByName('Collation').AsString+'"';
        if (qu1.FieldByName('BlobCompression').AsInteger <> 0)
        then col := col +' COMPRESSION '+qu1.FieldByName('BlobCompression').AsString;
     end
     else
     if (qu1.FieldByName('Type').AsString='Decimal')
     then col := col + '
'+qu1.FieldByName('Type').AsString+'(20,'+qu1.FieldByName('Scale').AsString+')'
     else col := col + ' '+UpperCaseString(qu1.FieldByName('Type').AsString);

     if qu1.FieldByName('Generated').AsBoolean
     and (not qu1.FieldByName('Identity').AsBoolean)
     then col := col + ' GENERATED '+qu1.FieldByName('GeneratedWhen').AsString+
                       ' AS '+qu1.FieldByName('GenerateExpr').AsString
     else
     if qu1.FieldByName('Identity').AsBoolean
     then col := col + ' GENERATED '+qu1.FieldByName('GeneratedWhen').AsString+' AS
IDENTITY (START WITH '+
                       qu1.FieldByName('IdentitySeed').AsString+', '+'INCREMENT BY '+
                       qu1.FieldByName('IdentityIncrement').AsString+')'
     else
     if qu1.FieldByName('Computed').AsBoolean
     then col := col + ' COMPUTED ALWAYS AS '+qu1.FieldByName('ComputeExpr').AsString
     else
     if (not qu1.FieldByName('DefaultExpr').IsNull)
     then col := col + ' DEFAULT '+qu1.FieldByName('DefaultExpr').AsString;

     if (not qu1.FieldByName('Nullable').AsBoolean)
     then col := col + ' NOT NULL';

     if (not qu1.FieldByName('Description').IsNull)
     then col := col + ' DESCRIPTION '+quotedstr(qu1.FieldByName('Description').AsString);

     qu1.Next;

     if (not qu1.EOF)
     then col := col + ',' + nl;

  end;
  qu1.Close;

  qu1.SQL.Text := 'select * from information.tables where
name='+Engine.QuotedSQLStr(TableName);
  qu1.Open;
  result := 'CREATE TABLE "'+TableName+'"'+nl+'('+nl+col;
  if con <> ''
  then result := result + ','+nl+con;
  result := result + nl + ')';

  if (not qu1.FieldByName('Description').IsNull)
  then result := result + nl + 'DESCRIPTION
'+quotedstr(qu1.FieldByName('Description').AsString);

  result := result + nl + 'VERSION
'+Engine.CurrToSQLStr(qu1.FieldByName('Version').AsCurrency);
  if qu1.FieldByName('Encrypted').AsBoolean
  then result := result + nl + 'ENCRYPTED'
  else result := result + nl + 'UNENCRYPTED';
  result := result + nl + 'INDEX PAGE SIZE '+qu1.FieldByName('IndexPageSize').AsString;
  result := result + nl + 'BLOB BLOCK SIZE '+qu1.FieldByName('BlobBlockSize').AsString;
  result := result + nl + 'MAX ROW BUFFER SIZE
'+qu1.FieldByName('MaxRowBufferSize').AsString;
  result := result + nl + 'MAX INDEX BUFFER SIZE
'+qu1.FieldByName('MaxIndexBufferSize').AsString;
  result := result + nl + 'MAX BLOB BUFFER SIZE
'+qu1.FieldByName('MaxBlobBufferSize').AsString;

  result := result + ';'+nl+nl+nl;
  qu1.Close;

  qu1.SQL.Text:='select * from Information.Indexes where
TableName='+Engine.QuotedSQLStr(TableName);
  qu1.Open;
  while not qu1.Eof do begin
     if (qu1.FieldByName('Type').AsString='Index')
     or (qu1.FieldByName('Type').AsString='Text Index')
     then begin
        if (qu1.FieldByName('Type').AsString='Index')
        then result := result + 'CREATE INDEX'
        else if (qu1.FieldByName('Type').AsString='Text Index')
        then result := result + 'CREATE TEXT INDEX';
        result := result + ' "' + qu1.FieldByName('Name').AsString + '" ON "' +
                           qu1.FieldByName('TableName').AsString+'"';

        col := '';
        qu2.SQL.Text:='select * from Information.IndexColumns where
TableName='+Engine.QuotedSQLStr(TableName)+' and '+
                      'IndexName='+Engine.QuotedSQLStr(qu1.FieldByName('name').AsString);
        qu2.Open;
        while not qu2.Eof do begin
           if (col='')
           then col := col + '("' + qu2.FieldByName('ColumnName').AsString+'"'
           else col := col + ', "' + qu2.FieldByName('ColumnName').AsString+'"';

           if (not qu2.FieldByName('Collation').IsNull)
           then col := col + ' COLLATE "' + qu2.FieldByName('Collation').AsString+'"';

           if qu2.FieldByName('Descending').AsBoolean
           then col := col + ' DESC';

           qu2.Next;
        end;
        col := col + ')';

        result := result + nl + col;

        if (not qu1.FieldByName('Description').IsNull)
        then result := result + nl + 'DESCRIPTION ' +
quotedstr(qu1.FieldByName('Description').AsString);

        if (qu1.FieldByName('Type').AsString='Text Index')
        then begin
           result := result + nl + 'INDEXED WORD LENGTH ' +
qu1.FieldByName('IndexedWordLength').AsString;
           if (not qu1.FieldByName('FilterTypeColumn').IsNull)
           then result := result + nl + 'FILTER TYPE COLUMN "' +
qu1.FieldByName('FilterTypeColumn').AsString+'"';

           result := result + nl + 'WORD GENERATOR "' +
qu1.FieldByName('WordGenerator').AsString+'"';
        end;
        result := result + ';' + nl+nl+nl;

     end;
     qu1.Next
  end;
  qu1.Close;

  qu1.Free;
  qu2.Free;

end;

CREATE TABLE "FINALDB"
(       
LIKE "ORIGINALDB"
INCLUDING GENERATED
INCLUDING IDENTITY
INCLUDING DEFAULTS
)

work well, but there are 2 problems :


* There are not :   INCLUDING CONSTRAINTS and INCLUDING INDICES (
 i need a perfect copy.


* Original and Destination table are in 2 Different Database / Directory
  how i can indicate database ?


CREATE TABLE "DB2.FINALDB" (   LIKE "DB1.ORIGINALDB" )
This don't work.  DB1 .. DB2...error...

Mauro

>* There are not : INCLUDING CONSTRAINTS and INCLUDING INDICES (
> i need a perfect copy.

Tim is aware of those.

>* Original and Destination table are in 2 Different Database / Directory
> how i can indicate database ?
>
>
>CREATE TABLE "DB2.FINALDB" ( LIKE "DB1.ORIGINALDB" )
>This don't work. DB1 .. DB2...error...

You set the query to the destination database and just use the table name, you reference the source database as a prefix for the source table. I don't know if it was just a typo in the post but "DB1.ORIGINALDB"  should be "DB1"."ORIGINALDB"

Roy Lambert [Team Elevate]

ps I'd still like to know why you can't use EDBManager

Mauro,

<< Any more info ? >>

Check out the edbreverse.pas unit that ships with EDB in the \code\source
subdirectory.  It's the same code that is used by the EDB Manager.

--
Tim Young
Elevate Software
www.elevatesoft.com