Oracle Pl/SQL alter table columns

Question

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!

Possible duplicate of [How can I describe a table in Oracle without using the DESCRIBE command?](https://stackoverflow.com/questions/9855209/how-can-i-describe-a-table-in-oracle-without-using-the-describe-command) — Serg, Jul 13 '17 at 11:11

score 1 · Answer 1 · answered Oct 29 '18 at 10:35

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

score 0 · Answer 2 · answered Jul 13 '17 at 11:10

0

If you only need to get the properties.

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

answered Jul 13 '17 at 11:10

Sudipta Mondal

2,550
1
19
20

I need to generate a syntax from table1 similar with: ALTER TABLE table1 MODIFY (column DEFAULT '0'); – Catalin Jul 13 '17 at 11:13
@Catalin - Unless you have some sort of **auditing** setup, you cannot find that information. – Sudipta Mondal Jul 13 '17 at 11:16
You will be better off getting the DDL and including the 'as select' in the DDL to create your table. I have never seen something that generates 'alter table' statements, which you can script with a bit of plsql but why reinvent the wheel? – sandman Jul 13 '17 at 12:03
I use this to generate batch: Select TABLE_NAME, COLUMN_NAME, DATA_DEFAULT ,('ALTER TABLE ' || TABLE_NAME || ' MODIFY (' || COLUMN_NAME || ' DEFAULT ' || TRIM((CASE WHEN c.DEFAULT_LENGTH IS NULL THEN '0' ELSE extractvalue(dbms_xmlgen.getxmltype('select data_default from user_tab_columns where table_name = ''' || c.TABLE_NAME || ''' and column_name = ''' || c.COLUMN_NAME || ''''), '//text()') END) || ')' )) AS SYNTAX from DBA_TAB_COLUMNS C where TABLE_NAME IN (select table from list_of_tables) AND DATA_DEFAULT IS NOT NULL; – Catalin Jul 13 '17 at 13:02

score 0 · Answer 3 · answered Jul 13 '17 at 11:26

0

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';

answered Jul 13 '17 at 11:26

CompEng

7,161
16
68
122

Oracle Pl/SQL alter table columns

3 Answers3

try this: