SELECT MAX(column_id) FROM all_Tab_Cols where table_name in(...)
当前用户: SQL> select * 2 from 3 ( 4 select table_name,count(*) count 5 from user_tab_columns 6 group by table_name 7 order by 2 desc 8 ) 9 where rownum=1;TABLE_NAME COUNT ------------------------------ ---------- MVIEW$_ADV_PLAN 27整个数据库: SQL> select * 2 from 3 ( 4 select table_name,count(*) count 5 from dba_tab_columns 6 group by table_name 7 order by 2 desc 8 ) 9 where rownum=1;TABLE_NAME COUNT ------------------------------ ---------- GV_$DISPATCHER_RATE 67
select (a.columnsnum), a.table_name from( select max(atc.column_id) columnsnum, atc.table_name from all_tab_cols atc where atc.owner='SCOTT' group by atc.table_name order by columnsnum desc )a where rownum<5
SQL> select *
2 from
3 (
4 select table_name,count(*) count
5 from user_tab_columns
6 group by table_name
7 order by 2 desc
8 )
9 where rownum=1;TABLE_NAME COUNT
------------------------------ ----------
MVIEW$_ADV_PLAN 27整个数据库:
SQL> select *
2 from
3 (
4 select table_name,count(*) count
5 from dba_tab_columns
6 group by table_name
7 order by 2 desc
8 )
9 where rownum=1;TABLE_NAME COUNT
------------------------------ ----------
GV_$DISPATCHER_RATE 67
SQL> select distinct atc.column_id,atc.table_name from all_tab_cols atc where atc.owner='SCOTT' order by atc.column_id desc; COLUMN_ID TABLE_NAME
---------- ------------------------------
8 EMP
7 EMP
7 V_EMP_DEM
6 AUTOMOBILES
6 EMP
6 EMPLOYEES
6 V_EMP_DEM
5 AUTOMOBILES
5 DE_BST_DETAILS
5 EMP
5 EMPLOYEES
5 PRODUCT
5 PRODUCT_TEMP
5 V_EMP_DEM
4 AUTOMOBILES
4 BONUS
4 DE_BST_DETAILS
4 EMP
4 EMPLOYEES
4 EXAMPLE COLUMN_ID TABLE_NAME
---------- ------------------------------
4 PRODUCT
4 PRODUCT_TEMP
4 V_EMP_DEM
3 AUTOMOBILES
3 BONUS
3 DEPT
3 DEPT1
3 DE_BST_DETAILS
3 EMP
3 EMPLOYEES
3 EXAMPLE
3 PRODUCT
3 PRODUCT_TEMP
3 SALGRADE
3 TB1
3 TB2
3 V_EMP_DEM
2 AUTOMOBILES
2 B1
2 BONUS
2 DEPARTMENTS COLUMN_ID TABLE_NAME
---------- ------------------------------
2 DEPT
2 DEPT1
2 DE_BST_DETAILS
2 EMP
2 EMPLOYEES
2 EXAMPLE
2 NEW_EMP
2 PRODUCT
2 PRODUCT_TEMP
2 SALGRADE
2 TB1
2 TB2
2 V_EMP_DEM
1 A
1 A1
1 AUTOMOBILES
1 B1
1 BONUS
1 DEPARTMENTS
1 DEPT
1 DEPT1 COLUMN_ID TABLE_NAME
---------- ------------------------------
1 DE_BST_DETAILS
1 EMP
1 EMPLOYEES
1 EXAMPLE
1 NEW_EMP
1 PRODUCT
1 PRODUCT_TEMP
1 SALGRADE
1 TB1
1 TB2
1 TEST111
1 V_EMP_DEM74 rows selected最前面的那条数据就是你要的信息,有列个数以及表名字!
from(
select max(atc.column_id) columnsnum, atc.table_name from all_tab_cols atc where atc.owner='SCOTT' group by atc.table_name order by columnsnum desc
)a where rownum<5