CODEWITHSUNDEEP
sqloracle-database
Catalin
Catalin

Reputation: 263

Oracle Pl/SQL alter table columns

I have created a table2 with SQL query: create table2 as select * from table1

Create table as select ... doesn't retain all column properties from table1 in table2.

Is it possible to generate an SQL file to ALTER table2 column properties (DATA_LENGTH,DATA_PRECISION,DATA_SCALE,DEFAULT_LENGTH,DATA_DEFAULT) with all column properties from table1?

Thanks!

Upvotes: 0

Views: 529

Answers (3)

Sameer Pradhan
Sameer Pradhan

Reputation: 135

--drop table table2;

create table table1 (
 x number(10, 2) default 10,
 y varchar2(200) default 'NA'
);

create table table2 as select * from table1;

Let us try for data_default:

create table search_user_tab_columns as 
select  table_name, column_name, to_lob(DATA_DEFAULT) dd
  from user_tab_columns
 where table_name = 'TABLE1';

As data_default is a long data in user_tab_columns we have to create an intermediate 

search_user_tab_columns (dummy) :-( 
select 'ALTER TABLE "' || table_name || '" MODIFY "' || column_name || '" DEFAULT ' || Dd || ';'
  from search_user_tab_columns
 where table_name = 'TABLE1';

Note:

SELECT DBMS_METADATA.GET_DDL('TABLE','<TABLE_NAME>','<SCHEMA_NAME>') from dual;

This is better way of doing this if creating a table from an existing table . But sometimes you may try this for specific purposes.

Upvotes: 1

CompEng
CompEng

Reputation: 7416

try this:

you can see all properties

select column_name as "Name"
     , nullable as "Null?"
     , concat(concat(concat(data_type,'('),data_length),')') as "Type"
  from user_tab_columns
 where table_name = 'MY_TABLE';

Upvotes: 0

Sudipta Mondal
Sudipta Mondal

Reputation: 2572

If you only need to get the properties. 

SELECT DBMS_METADATA.GET_DDL('TABLE','<TABLE_NAME>','<SCHEMA_NAME>') from dual;

Upvotes: 0

Related Questions