All Forums Database
eejimkos 73 posts Joined 01/12
22 Feb 2013
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.

mohan.mscss 31 posts Joined 04/11
22 Feb 2013

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
 

eejimkos 73 posts Joined 01/12
25 Feb 2013

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.

VandeBergB 182 posts Joined 09/06
25 Feb 2013

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.... :)

Some drink from the fountain of knowledge, others just gargle.

You must sign in to leave a comment.