Need Help in creating a copy of the Table structure without any secondary indexes and Partitions

Database
Enthusiast

Need Help in creating a copy of the Table structure without any secondary indexes and Partitions

Hi ,

I am trying to copy a table structure from one database to another without data ,stats and secondary Indexes and Partion .

I know we use  Create Table DB1.TB1 AS DB2.TB2 WITH NO DATA; But it copies the secondary Indexes and Partion also,

I want to Avoid those. I am going to pass parameters for Databasenames and Tablenames , So Please advice me if you have any correct Ideas.

6 REPLIES
mjj
Teradata Employee

Re: Need Help in creating a copy of the Table structure without any secondary indexes and Partitions

Hi,

This can help:

Create Table DB1.TB1 AS (select * from DB2.TB2) WITH NO DATA;

It will copy the table structure of TB1 to TB2.

Say col2 is the PI of TB1. But above statement chooses first column of TB1 as PI of TB2 by default.

To keep the appropriate PI, we need to set it in the create table only.

Create Table DB1.TB1 AS (select * from DB2.TB2) WITH NO DATA primary index(col2);

Enthusiast

Re: Need Help in creating a copy of the Table structure without any secondary indexes and Partitions

Thanks mjj,

But My consern is: As I said already I am going to use this script in Stored procedure.(This is not a individual stamnt).

I will just pass two parameters DB2 and TB2 and I need to create the same structure in DB1 (eg:- DB1.TB2) with no secondary Indexes.

Is there any chance that I can go to DBC.Indeces to find the Primary Index of DB2.TB2 and Place that in :

Create Table DB1.TB1 AS (select * from DB2.TB2) WITH NO DATA primary index(:col2);?

Any Ideas how can I proceed with the logic?

Enthusiast

Re: Need Help in creating a copy of the Table structure without any secondary indexes and Partitions

Use the logic applied in this macro:

Replace Macro Test123

(

databasename1 varchar (30),

databasename2 varchar (30)

)

as

(

sel

'Create Table '||trim(:databasename1)||'.'||trim(tablename)||' AS (select * from '||trim(:databasename2)||'.'||trim(tablename)||') WITH NO DATA  primary index ('

||MAX(CASE WHEN COLUMNPOSITION = 1 THEN TRIM(COLUMNNAME) ELSE '' END) ||

            MAX(CASE WHEN COLUMNPOSITION = 2 THEN ',' || TRIM(COLUMNNAME) ELSE ''     END) ||

            MAX(CASE WHEN COLUMNPOSITION = 3 THEN ',' || TRIM(COLUMNNAME) ELSE ''     END) ||

            MAX(CASE WHEN COLUMNPOSITION = 4 THEN ',' || TRIM(COLUMNNAME) ELSE ''     END) ||

            MAX(CASE WHEN COLUMNPOSITION = 5 THEN ',' || TRIM(COLUMNNAME) ELSE ''    END) ||

            MAX(CASE WHEN COLUMNPOSITION = 6 THEN ',' || TRIM(COLUMNNAME) ELSE ''    END) ||

            MAX(CASE WHEN COLUMNPOSITION = 7 THEN ',' || TRIM(COLUMNNAME) ELSE ''    END) ||

            MAX(CASE WHEN COLUMNPOSITION = 8 THEN ',' || TRIM(COLUMNNAME) ELSE ''     END) ||

            MAX(CASE WHEN COLUMNPOSITION = 9 THEN ',' || TRIM(COLUMNNAME) ELSE ''     END) ||

            MAX(CASE WHEN COLUMNPOSITION = 10 THEN ',' || TRIM(COLUMNNAME) ELSE''      END) ||

           MAX(CASE WHEN COLUMNPOSITION = 11 THEN ',' || TRIM(COLUMNNAME) ELSE ''      END) ||

            MAX(CASE WHEN COLUMNPOSITION = 12 THEN ',' || TRIM(COLUMNNAME) ELSE ''      END) ||

            MAX(CASE WHEN COLUMNPOSITION = 13 THEN ',' || TRIM(COLUMNNAME) ELSE ''      END) ||

            MAX(CASE WHEN COLUMNPOSITION = 14 THEN ',' || TRIM(COLUMNNAME) ELSE ''      END) ||

            MAX(CASE WHEN COLUMNPOSITION = 15 THEN ',' || TRIM(COLUMNNAME) ELSE ''      END) ||

            MAX(CASE WHEN COLUMNPOSITION = 16 THEN ',' || TRIM(COLUMNNAME) ELSE ''      END) ||

            MAX(CASE WHEN COLUMNPOSITION > 16  THEN ',...' ELSE '' END)           ||');'

from dbc.indices

 WHERE DATABASENAME= :databasename2

 and IndexType='P'

                                    GROUP BY

            DATABASENAME,

            TABLENAME ;

            )   ;

exec Test123 ('Test1','Test2');

Enthusiast

Re: Need Help in creating a copy of the Table structure without any secondary indexes and Partitions

Thanks Joy,

But It will take only the first Column as the Primary Index.

What if My Original Table has Combination of Two columns as Primary Index?

What if My Original Table has 2nd / 3rd column as Unique Primary Index?

Enthusiast

Re: Need Help in creating a copy of the Table structure without any secondary indexes and Partitions

I am not using dbc.columns,

I am using dbc.indices... on top of that I have a filter for Primary Index... 

You can create the macro and try executing it...

mjj
Teradata Employee

Re: Need Help in creating a copy of the Table structure without any secondary indexes and Partitions

Hi,

You can take column "columnname" from dbc.indices and store it in a variable and then use that variable in your query.

Regards,