exact table clone

Database
Enthusiast

exact table clone

Hello,

Thank you in advance for your time.

I want to create a table with the same structure as an existing one.

The problem with the create table command , is that it does take under consideration foreign keys (for my case).

(

So , how can i achieve this dynamic? Meaning,i want to backup a lot of tables with the same dll,data by just changing the name with the same logic

(table x --> c_b  ,table y --> y_b and so on....)

1.just create the ddl  and then add the foreign key?

2.Can i import the show ddl into a column in order to change anything i want with code?

Thank you very much.

3 REPLIES
Enthusiast

Re: exact table clone

if i read you correctly, hope you are looking as like below, please let know if otherwise

ct new_tablename as exissting_tblname with data and stats unique primary index(alt_id);

Regards,

Mohan K

Enthusiast

Re: exact table clone

Thank you very much for your time.

This syntax is ok , but is does not copy the foreign key of the  initial table.

So, there is no view to get the whole ddl , but i have to access all these who keep some info about,columns - indexes- datatype...and so on.

Thanks.

Enthusiast

Re: exact table clone

The following code will generate "show table" statements for all of the tables in the specified database.  You can export the answer set to a .txt file from QueryMan and run the statements in the generated text file to see the DDL, constraints included.

SELECT 
'show table '||TRIM(databasename)||'.'||TRIM(tablename)|| ';'
FROM
dbc.tables
WHERE databasename = '<insert your dbname here>'

You can change the export settings in QueryMan to create a clean script from the execution of the "show table" batch with a little bit of exploration, which is left as an exercise for you.... :)