select count (*) from TABELAS_DO_USUARIO

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
Post Reply
MarcioRM
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Posts: 3
Joined: Tue, 11 Apr 2006 3:28 pm
Location: Curitiba / PR

Good afternoon to all

This is my first post here:

I do not have experience with SQL, but I need a lot of one that returns me a count (* ) of current user tables, more or less like this:

Table 01 = 1789
Table 02 = 3010
Table 03 = 5890
Table 04 = 2410
...

If you have something ready to tell me this tell me and sorry for the unnecessary post.

Thanks for the attention
User avatar
dr_gori
Moderador
Moderador
Posts: 5024
Joined: Mon, 03 May 2004 3:08 pm
Location: Portland, OR USA
Contact:
Thomas F. G

Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered

Well, there is nothing automatic that does this ...

The most automatic you have is for you to consult the User_Tables. In this dictionary table, it has a field called num_rows, which has the amount of rows of the table.

See:

Select all

SQL> select table_name, num_rows, last_analyzed 
  2  from user_tables 
  3  order by num_rows desc nulls last 
  4  / 
 
TABLE_NAME                       NUM_ROWS LAST_ANAL 
------------------------------ ---------- --------- 
TB_EMBARQUE_DEMONSTRATIVO        79894226 26-MAR-06 
TB_EMBARQUE                      78932637 26-MAR-06 
TB_LOG_RECEPCAO_AVERBCOL         29269790 26-MAR-06 
TB_LOG_EMBARQUE                  18651547 26-MAR-06
Note that the largest table here has 79 million lines!
Of course this information is false, because this Num_rows field corresponds to the number of lines that had the last analyze the table. That is, if you do not collect statistics on your bank periodically, you may have information very wrong in this field.

Let's prove this:
Note that I did the count now and my table has 81 million lines! (against 79 mi collected on March 26)

Select all

SQL> select count(*) from TB_EMBARQUE_DEMONSTRATIVO; 
 
  COUNT(*) 
---------- 
  81159845 
 
SQL> 



Another way to do this, is you create A script that generates SQL's with count. Thus:

Select all

SQL> select 'SELECT COUNT(*) FROM '||table_name||';' 
  2  from user_tables 
  3  order by num_rows desc nulls last 
  4  / 
 
'SELECTCOUNT(*)FROM'||TABLE_NAME||';' 
---------------------------------------------------- 
SELECT COUNT(*) FROM TB_EMBARQUE_DEMONSTRATIVO; 
SELECT COUNT(*) FROM TB_EMBARQUE; 
SELECT COUNT(*) FROM TB_LOG_RECEPCAO_AVERBCOL; 
SELECT COUNT(*) FROM TB_LOG_EMBARQUE; 
SELECT COUNT(*) FROM TT_LOG_EXCLUSAO; 
SELECT COUNT(*) FROM TT_RECEPCAO_AVERBCOL; 
SELECT COUNT(*) FROM TT_RECEPCAO_AVERBCOL_OK; 
SELECT COUNT(*) FROM TT_CONCENTRACAO_RISCO; 
SELECT COUNT(*) FROM TB_RATEIO_MOVIMENTO; 
SELECT COUNT(*) FROM TB_COMISSAO; 
SELECT COUNT(*) FROM TB_AUTO_COBERTURA; 
SELECT COUNT(*) FROM TB_PARCELA_LOG; 
SELECT COUNT(*) FROM TB_RATEIO_MOVIMENTO_CWI; 
SELECT COUNT(*) FROM TB_MOVIMENTO_FINANCEIRO; 
SELECT COUNT(*) FROM TB_LOG_LIQ_MOVIMENTO; 
SELECT COUNT(*) FROM TB_ARQUIVO_EMBARQUE; 
SELECT COUNT(*) FROM TB_MOVIMENTO_FINANCEIRO_CWI; 
SELECT COUNT(*) FROM TB_RATEIO_GAP; 
SELECT COUNT(*) FROM TB_ERRO_AVERBNET; 
SELECT COUNT(*) FROM TB_PERC_COMISSAO_LOG; 
SELECT COUNT(*) FROM TB_PARCELA_RECIBO; 
SELECT COUNT(*) FROM TB_PARCELA_NOTA; 
SELECT COUNT(*) FROM TB_RATEIO_MOVIMENTO_FABIO; 
SELECT COUNT(*) FROM TB_MOVIMENTO_FINANCEIRO_FABIO; 
SELECT COUNT(*) FROM TB_ITEM_PROPOSTA_CONTRATO; 
SELECT COUNT(*) FROM TB_REGRA_PROCESSO_SEGURADO; 
SELECT COUNT(*) FROM TB_PARCELA; 
SELECT COUNT(*) FROM TB_GAP; 
SELECT COUNT(*) FROM TB_LOG_LIQ_MOVIMENTO_FABIO; 

Hence, just run the script, :-)

: -O
MarcioRM
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Posts: 3
Joined: Tue, 11 Apr 2006 3:28 pm
Location: Curitiba / PR

Thank you so much Dr_Gori

One more thing ...: D

How I could do to display which table is every "count".
I generated the script by setting the spool file.txt for the two cases,
but the file with the totals was generated without the table name, thus:

[0]]

Select all

  COUNT(*) 
---------- 
         6
would be better:

Select all

SELECT COUNT(*) FROM TABELA)! 
  COUNT(*) 
---------- 
     87855 
     

Select all

SELECT COUNT(*) FROM TABELA)! 
  COUNT(*) 
---------- 
         6
How could I do this?

Thanks for the attention
User avatar
dr_gori
Moderador
Moderador
Posts: 5024
Joined: Mon, 03 May 2004 3:08 pm
Location: Portland, OR USA
Contact:
Thomas F. G

Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered

What stewardship this forum, huh?

Select all

select 'SELECT '''||table_name||': '' TABELA, COUNT(*) FROM '||table_name||';' 
from user_tables 
order by num_rows desc nulls last
: -O
MarcioRM
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Posts: 3
Joined: Tue, 11 Apr 2006 3:28 pm
Location: Curitiba / PR

Thank you so much Dr_Gori.

I hope a few times can be collaborating with the forum instead of only taking advantage of his stewards: D

Thanks
Post Reply
  • Information
  • Who is online

    Users browsing this forum: Bing [Bot] and 9 guests