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 Go to next message
sekharsomu
Messages: 71
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3087
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 Go to previous messageGo to next message
Michel Cadot
Messages: 67988
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 Go to previous messageGo to next message
Michel Cadot
Messages: 67988
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3087
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.
Re: Listing table names,noofrows,noofcolumns primarkeyscol in a schema [message #684520 is a reply to message #684518] Mon, 21 June 2021 21:55 Go to previous message
sekharsomu
Messages: 71
Registered: December 2008
Member
Thanks just woke up to see so many responses thank you for your time and help
Will work on them shortly
Previous Topic: Automate repartitions
Next Topic: two different counts in select
Goto Forum:
  


Current Time: Fri Oct 15 19:14:26 CDT 2021