常见SQL

查询表字段结构（除DESC）

SELECT   *
    FROM all_tab_columns
   WHERE table_name = UPPER (tableName)
ORDER BY column_id;

--------------------------------------------------------

SELECT      ''''
         || column_name
         || ''''
         || ' as column'
         || column_id
         || ','
         || column_name
         || ' as olddata'
         || column_id
         || ','
         || column_name
         || ' as newdata'
         || column_id
         || ','
    FROM all_tab_columns
   WHERE owner = scm 
   and table_name = UPPER (tableName)
ORDER BY column_id;

 

 

建表导表结构---->>>

1.查询要导出的表

select * from dba_tables where (table_name like '#%' or table_name like '#%') and owner='SYSTEM'

2.导出建表语句

select dbms_metadata.get_ddl('TABLE','表名','SYSTEM') from dual;