asked on

Add sequence number to a query.

Experts,

There are 203 Tables in my Schema. The code below gives me the count of number of records in each table.

declare
v_rowcnt integer;
begin
for t in (select table_name from user_tables order by 1) loop
execute immediate 'select count(*) from ' || t.table_name into v_rowcnt;
dbms_output.put_line(rpad(t.table_name,40,'.') || v_rowcnt);
end loop;
end;
/

--############################################

The first few lines of the output look like this:

ACCOUNT.................................1
AD......................................7
AD_AGE_STATED...........................6
AD_BODY.................................7
AD_CATEGORY.............................1
AD_LOCATION_STATED......................7
AD_LOCATION_WEBSITE.....................7
AD_NAME_ADVERTISED......................1

--#####################

I would like the output to look below. Add another column to the right (First Column) to tell the sequence number of the table. How do I modify the script above to get the desired result.

1 ACCOUNT.................................1
2 AD......................................7
3 AD_AGE_STATED...........................6
4 AD_BODY.................................7
5 AD_CATEGORY.............................1
6 AD_LOCATION_STATED......................7
7 AD_LOCATION_WEBSITE.....................7
8 AD_NAME_ADVERTISED......................1

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Kamal Agnihotri

ASKER

Hi Slightwv,

With your guidance, the code below works. Would you written it any differently.
--#######################################
declare
v_row integer := 0;
v_rowcnt integer;
begin
for t in (select table_name from user_tables order by 1) loop
v_row := v_row + 1;
execute immediate 'select count(*) from ' || t.table_name into v_rowcnt;
dbms_output.put_line (rpad(v_row, 5, ' ') || (rpad(t.table_name,40,'.') || v_rowcnt));
end loop;
end;
/

slightwv (䄆 Netminder)

>>Would you written it any differently.

Yes.

I would use the XML trick in the thread I posted.

That is sort of why I posted it........

For ALL tables, remove my where clause. I just didn't want to return them all for this small test.

drop table tab1 purge;
drop table tab2 purge;
create table tab1(col1 char(1));
create table tab2(col1 char(1));
insert into tab1 values('a');
insert into tab2 values('a');
insert into tab2 values('a');
commit;


SELECT row_number() over(order by table_name) rn, table_name,
       TO_NUMBER(EXTRACTVALUE(
                     xmltype(DBMS_XMLGEN.getxml('select count(*) X from ' || table_name)),
                     '/ROWSET/ROW/X'
                 ))
           COUNT
FROM user_tables
where table_name like 'TAB%';

Open in new window