-
Selecting All Rows in a Schema.
Can someone help me write a SQL statmement that produces a return of all rows with a row count total in a given schema? Thanks!
-
You want to generate select count(*) from statement for all tables?
What dbms are you using?
-
Yes.
Oracle 8i
I need 1 singular statement to: 1. return a list of all rows in a given schema (apps for example) and 2. post a row count total at the borttom. Thanks!
-
rows of what?
got any exmples of what you want?
also, mixing column types ie. rows from a column and then a total at the bottom ususally means mixing in a procedure of some form or some interesting decodes or character conversions.
if you can give us where the data is coming from and what the desired output should look like, that might help.
-
You can get count of rows from dba_tables
select DECODE(GROUPING(table_name), 1, 'Total',Table_Name) AS Table_Name, sum(num_rows) from dba_tables where owner=&yourschema
group by rollup (table_name)
/
-
skhanal, just be cautious that if statistics haven't been run, there is no quarentee that the num_rows actually equal to the number of rows in the tables.
-
Agreed.
-
skhanal - the schema name is BOB - when I run the statement you gave me using that schema name it prompts me with "Enter value for bob:"
select DECODE(GROUPING(table_name), 1, 'Total',Table_Name)
AS Table_Name, sum(num_rows) from dba_tables
where owner=&BOB
group by rollup (table_name);
I'm not sure what to enter for a value.
-
Type BOB
Or if you don't want a dynamic query use this
select DECODE(GROUPING(table_name), 1, 'Total',Table_Name)
AS Table_Name, sum(num_rows) from dba_tables
where owner='BOB'
group by rollup (table_name);
-
skhanal, I'm getting closer, but there is no totals in the SUM column. And I know there are many rows in each of the 3 tables.
SQL> select DECODE(GROUPING
table_name), 1, 'Total',Table_Name)
AS Table_Name, sum(num_rows) from dba_tables
where owner='BOB'
group by rollup (table_name);
TABLE_NAME SUM(NUM_ROWS)
------------------------------ -------------
T1
TEST_01
TEST_02
Total
SQL>
Thanks! Keek
-
As JKOOPMANN has mentioned, you must run statistics for all tables.
the command is :
ANALYZE TABLE table_name COMPUTE STATISTICS;
or for whole schema use
BEGIN
DBMS_STATS.GATHER_SCHEMA_STATS('BOB');
END;
Last edited by YuckFou; 04-02-2004 at 02:09 AM.
You Have To Be Happy With What You Have To Be Happy With (KC)
-
skhanal, koop, & yuck, I'm set now. The gather stats did the trick!
Thanks all!!
-
Yuck, you got funny name.
Here's the script that does not need statistics. Will run on any version where dbms_sql is available.
d
DECLARE
uname VARCHAR2(20) := 'BOB';
rec_cnt NUMBER;
tot_rec_cnt NUMBER := 0;
stmt VARCHAR2(240);
curs INTEGER;
fdbk INTEGER;
BEGIN
dbms_output.put_line('User '||uname);
dbms_output.put_line('Table, Record count');
stmt := 'select count(*) from '||uname||'.$TABLE$';
curs := dbms_sql.open_cursor;
for rec in (select TABLE_NAME from all_tables where owner = uname)
loop
dbms_sql.parse(curs,replace(stmt,'$TABLE$',rec.tab le_name),dbms_sql.native);
dbms_sql.define_column(curs,1,rec_cnt);
fdbk := dbms_sql.execute_and_fetch(curs);
dbms_sql.column_value(curs,1,rec_cnt);
dbms_output.put_line(rec.table_name||','||rec_cnt) ;
tot_rec_cnt := tot_rec_cnt + rec_cnt;
end loop;
dbms_output.put_line('Total records for '||uname||','||tot_rec_cnt);
dbms_sql.close_cursor(curs);
EXCEPTION WHEN OTHERS THEN
dbms_sql.close_cursor(curs);
dbms_output.put_line(stmt);
dbms_output.put_line(SQLERRM);
END;
-
Hi all
"Yuck" is not my name it's only nickname and for "funny" reasons I just added "Fou" and that's all story.
You Have To Be Happy With What You Have To Be Happy With (KC)
-
When I first saw your name in Database Journal, I did some jumble immediately.
It is quite amazing how some can change the officially unacceptable nick name to acceptable nick name.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|