Home » SQL & PL/SQL » SQL & PL/SQL » Listing table names,noofrows,noofcolumns primarkeyscol in a schema (oracle 12c)
Listing table names,noofrows,noofcolumns primarkeyscol in a schema [message #684514] |
Mon, 21 June 2021 15:03 |
sekharsomu
Messages: 72 Registered: December 2008
|
Member |
|
|
Problem:
I want to QUERY to generate a table like below:(ex
Quote:Tablename||noofrows||noofcolumns||PRIMARKEYCOL(IF ANY for the table)
xyz. 590. 11. xyz_id
bcd. 934 15 null
...
...
Sofar...
I was able to do this until now in 2 query:
Query 1:
select a.table_name,count_rows(a.table_name) total_rows,count(b.column_name) total_cols from user_tables a,
,user_tab_columns b
where a.table_name =b.table_name
and a.table_name not like('amp%')
group by a.table_name;
note:Count_rows() is function to calculate rows as stats are not up to date
query 2:
select b.table_name b.column_name PRIMKEY_COL FROM user_constraints a,user_cons_columns b
where
a.constraint_type = 'P'
and a.constraint_name=b.constraint_name
and a.table_name=b.table_name
and b.table_name not like ('amp%');
Problem
Now I need to merge this table to one query(as shown in example above) so that I can represent the data in one table. My issue in clubbing the table is, with joins and how to make sure table without any primary keys are represent because if I just directly give constraint type ='p' in the where clause of the join I see that it only shows table with Primarykeys I am not able to figure this out, kindly guide me or help me here .
Thank you for your time
|
|
|
Re: Listing table names,noofrows,noofcolumns primarkeyscol in a schema [message #684515 is a reply to message #684514] |
Mon, 21 June 2021 15:39 |
Solomon Yakobson
Messages: 3275 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Code below does what you need for all tables in a schema:
SELECT OWNER,
TABLE_NAME,
XMLCAST(
XMLQUERY(
'/ROWSET/ROW/CNT'
PASSING DBMS_XMLGEN.GETXMLTYPE(
'SELECT COUNT(*) CNT
FROM "' || OWNER || '". "' || TABLE_NAME || '"'
)
RETURNING CONTENT
)
AS NUMBER
) ROW_COUNT,
(
SELECT LISTAGG(CC.COLUMN_NAME,',') WITHIN GROUP(ORDER BY CC.POSITION)
FROM DBA_CONSTRAINTS C,
DBA_CONS_COLUMNS CC
WHERE C.OWNER = T.OWNER
AND C.TABLE_NAME = T.TABLE_NAME
AND C.CONSTRAINT_TYPE = 'P'
AND CC.OWNER = C.OWNER
AND CC.TABLE_NAME = C.TABLE_NAME
AND CC.CONSTRAINT_NAME = C.CONSTRAINT_NAME
) PK_COLUMNS
FROM DBA_TABLES T
WHERE OWNER = '&SCHEMA_NAME'
AND TABLE_NAME NOT LIKE 'MLOG$\_%' ESCAPE '\' -- exclude materialized view logs
AND NVL(IOT_TYPE,'NOT_IOT') NOT IN ('IOT_OVERFLOW','IOT_MAPPING') -- exclude IOT overflow and mapping
AND TEMPORARY = 'N' -- exclude temporary tables
AND NESTED = 'NO' -- exclude nested tables
AND SECONDARY = 'N' -- exclude Oracle Text index tables and other "non-tables"
AND EXTERNAL = 'NO' -- exclude external tables
AND HYBRID = 'NO' -- exclude hybrid partitioned tables
ORDER BY OWNER,
TABLE_NAME
/
SY.
|
|
|
Re: Listing table names,noofrows,noofcolumns primarkeyscol in a schema [message #684516 is a reply to message #684514] |
Mon, 21 June 2021 15:43 |
|
Michel Cadot
Messages: 68665 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
SQL> with
2 counts as (
3 select table_name,
4 xmlcast(
5 xmlquery(
6 ('count(fn:collection("oradb:/'||owner||'/'||table_name||'"))')
7 returning content)
8 as int) rows_in_table
9 from dba_tables
10 where owner = 'SCOTT'
11 ),
12 cols as (
13 select table_name, count(*) nb_cols
14 from dba_tab_columns
15 where owner = 'SCOTT'
16 group by table_name
17 ),
18 pk as (
19 select cons.table_name, col.column_name
20 from dba_constraints cons, dba_cons_columns col
21 where cons.owner = 'SCOTT' and cons.constraint_type = 'P'
22 and col.owner = 'SCOTT' and col.constraint_name = cons.constraint_name
23 )
24 select cnt.table_name, cnt.rows_in_table, cols.nb_cols, pk.column_name pk_col
25 from counts cnt inner join cols on cols.table_name = cnt.table_name
26 left outer join pk on pk.table_name = cnt.table_name
27 order by 1
28 /
TABLE_NAME ROWS_IN_TABLE NB_COLS PK_COL
------------------------------ ------------- ---------- ------------------------------
BONUS 0 4
DEPT 4 3 DEPTNO
EMP 15 8 EMPNO
SALGRADE 5 3
|
|
|
Re: Listing table names,noofrows,noofcolumns primarkeyscol in a schema [message #684517 is a reply to message #684516] |
Mon, 21 June 2021 15:45 |
|
Michel Cadot
Messages: 68665 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Or, if PK can have several columns:
SQL> with
2 counts as (
3 select table_name,
4 xmlcast(
5 xmlquery(
6 ('count(fn:collection("oradb:/'||owner||'/'||table_name||'"))')
7 returning content)
8 as int) rows_in_table
9 from dba_tables
10 where owner = 'SCOTT'
11 ),
12 cols as (
13 select table_name, count(*) nb_cols
14 from dba_tab_columns
15 where owner = 'SCOTT'
16 group by table_name
17 ),
18 pk as (
19 select cons.table_name,
20 listagg(col.column_name,',') within group (order by col.position) pk_cols
21 from dba_constraints cons, dba_cons_columns col
22 where cons.owner = 'SCOTT' and cons.constraint_type = 'P'
23 and col.owner = 'SCOTT' and col.constraint_name = cons.constraint_name
24 group by cons.table_name
25 )
26 select cnt.table_name, cnt.rows_in_table, cols.nb_cols, pk.pk_cols
27 from counts cnt inner join cols on cols.table_name = cnt.table_name
28 left outer join pk on pk.table_name = cnt.table_name
29 order by 1
30 /
TABLE_NAME ROWS_IN_TABLE NB_COLS PK_COLS
------------------------------ ------------- ---------- ------------------------------
BONUS 0 4
DEPT 4 3 DEPTNO
EMP 15 8 EMPNO
SALGRADE 5 3
[Updated on: Mon, 21 June 2021 15:46] Report message to a moderator
|
|
|
Re: Listing table names,noofrows,noofcolumns primarkeyscol in a schema [message #684518 is a reply to message #684515] |
Mon, 21 June 2021 15:45 |
Solomon Yakobson
Messages: 3275 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Missed you also want column count:
SELECT OWNER,
TABLE_NAME,
XMLCAST(
XMLQUERY(
'/ROWSET/ROW/CNT'
PASSING DBMS_XMLGEN.GETXMLTYPE(
'SELECT COUNT(*) CNT
FROM "' || OWNER || '". "' || TABLE_NAME || '"'
)
RETURNING CONTENT
)
AS NUMBER
) ROW_COUNT,
(
SELECT COUNT(*)
FROM DBA_TAB_COLUMNS TC -- change to DBA_TAB_COLS depending on your requirements
WHERE TC.OWNER = T.OWNER
AND TC.TABLE_NAME = T.TABLE_NAME
) COL_COUNT,
(
SELECT LISTAGG(CC.COLUMN_NAME,',') WITHIN GROUP(ORDER BY CC.POSITION)
FROM DBA_CONSTRAINTS C,
DBA_CONS_COLUMNS CC
WHERE C.OWNER = T.OWNER
AND C.TABLE_NAME = T.TABLE_NAME
AND C.CONSTRAINT_TYPE = 'P'
AND CC.OWNER = C.OWNER
AND CC.TABLE_NAME = C.TABLE_NAME
AND CC.CONSTRAINT_NAME = C.CONSTRAINT_NAME
) PK_COLUMNS
FROM DBA_TABLES T
WHERE OWNER = '&SCHEMA_NAME'
AND TABLE_NAME NOT LIKE 'MLOG$\_%' ESCAPE '\' -- exclude materialized view logs
AND NVL(IOT_TYPE,'NOT_IOT') NOT IN ('IOT_OVERFLOW','IOT_MAPPING') -- exclude IOT overflow and mapping
AND TEMPORARY = 'N' -- exclude temporary tables
AND NESTED = 'NO' -- exclude nested tables
AND SECONDARY = 'N' -- exclude Oracle Text index tables and other "non-tables"
AND EXTERNAL = 'NO' -- exclude external tables
AND HYBRID = 'NO' -- exclude hybrid partitioned tables
ORDER BY OWNER,
TABLE_NAME
/
SY.
|
|
|
|
Goto Forum:
Current Time: Sun Jun 30 23:16:06 CDT 2024
|