Hi Team,
   Is there some way to get the full Table creation sql including Constraints, Indexes & Triggers?
   I just spent a painful period replicating a whole pile of indexes into two backup tables.
   Would be much nicer if the 'Create Copy of Table' replicated all the original under the new name...

Regards,
Ian

Ian

>    Is there some way to get the full Table creation sql including Constraints, Indexes & Triggers?
>    I just spent a painful period replicating a whole pile of indexes into two backup tables.

Yes but its not built in. It can be done via Delphi since all the bits needed are there - look in the information tables - Tim has added a CreateSQL column so you can filter and add all the bits you need. The other approach is to use the Reverse Engineer Database and copy out the bits you want.

>    Would be much nicer if the 'Create Copy of Table' replicated all the original under the new name...

No. Unless its fully "optioned". Generally when I copy a table its either to use as an archive holder in which case the data just sits there and I don't want triggers etc or its to use as the base for a new table and having to go through and remove all the stuff I didn't want would be a pain.

Roy

Hi Roy,

Roy Lambert wrote:

> Ian
>
> >    Is there some way to get the full Table creation sql including Constraints, Indexes & Triggers?
> >    I just spent a painful period replicating a whole pile of indexes into two backup tables.
>
> Yes but its not built in. It can be done via Delphi since all the bits needed are there - look in the information
> tables - Tim has added a CreateSQL column so you can filter and add all the bits you need. The other approach is to
> use the Reverse Engineer Database and copy out the bits you want.

IB - The Reverse Engineer has potential.


>
> >    Would be much nicer if the 'Create Copy of Table' replicated all the original under the new name...
>
> No. Unless its fully "optioned". Generally when I copy a table its either to use as an archive holder in which case
> the data just sits there and I don't want triggers etc or its to use as the base for a new table and having to go
> through and remove all the stuff I didn't want would be a pain.

IB - It is easier to delete than re-type it all.  Perhaps the 'Copy' function could have the option to select which
elements are copied?  I have a 2 level Archive system.  The first level, Current to Archive, transfers all relevant
records to the Archive table.  These are accessible using a utility supplied.  Typically this would be done at 12
months and every month there after.  All indexes are maintained.  The second level, Archive to _Archive, transfers all
relevant records from Archive to _Archive tables.  All indexes are maintained.  This is typically done after 15 months
and every month thereafter.  These records are accessed using Manager.  In both cases the Customer can choose his
Archive/_Archive regime.

Ian

Ian

I had another idea whilst thinking of how easy it would be to write a Delphi utility



>IB - It is easier to delete than re-type it all.

You don't have to type much - a bit of copy'n'paste is all it takes -

SELECT  LIST(REPLACE('Accounts','ArchiveAccounts',CreateSQL),'!'+#13) FROM Information.Indexes WHERE TableName = 'Accounts'

SELECT  LIST(REPLACE('Accounts','ArchiveAccounts',CreateSQL),'!'+#13) FROM Information.Triggers WHERE TableName = 'Accounts'


run these in the database you're interested in, copy the contents of the column to the clipboard, add the final !, paste to a New sql window and run


Roy

ps that's the beauty of a catalog based system when you get used to it

Ian

<<   Is there some way to get the full Table creation sql including Constraints, Indexes & Triggers?>>
   I just spent a painful period replicating a whole pile of indexes into two backup tables.
   Would be much nicer if the 'Create Copy of Table' replicated all the original under the new name...


Do you know about the Reverse Engine Database feature? It will create an SQL file or a script with all objects of the database. It  can also compare two databases and create an upgrade SQL. Very useful.

Richard

Richard Harding wrote:
>
> Do you know about the Reverse Engine Database feature? It will create an SQL file or a script with all objects of the
> database. It  can also compare two databases and create an upgrade SQL. Very useful.
>
Hi Richard,
   Thanks for your input.  I did look at that and it is an option I will consider.
Regards,
Ian

Ian


When I get finished with fiddling with D2007 I'll knock up a little utility.

Roy Lambert

Ian,

<< Is there some way to get the full Table creation sql including Constraints, Indexes & Triggers? >>

There are a lot of different places to get reverse-engineered SQL in EDB, but in the Explorer treeview the big issue is the lack of multi-select support.  I need to get that in place, and then you can just drag and drop the selected indexes, etc. into the SQL window.

Tim Young
Elevate Software
www.elevatesoft.com

Tim

>There are a lot of different places to get reverse-engineered SQL in EDB, but in the Explorer treeview the big issue is the lack of multi-select support. I need to get that in place, and then you can just drag and drop the selected indexes, etc. into the SQL window.

Or alternatively just pop up a selection form when a table is dropped in a clean sql window. I'd much prefer to tick a few boxes on a form rather than drag'n'drop a load of stuff

Roy

+1