|
Tip of the Week
Tip for Week of January 11, 2004
List Table Names, Rows, and Created Date
This tip comes from
Pasupuleti Sailaja, in Hyderabad, Andhra Pradesh, India.
Description: -- Aim : To display table names, number of rows existed in table
-- and table created date in current login user for any ORACLE version
-- Usage : Step 1) All lines from given program save with filename DIR.SQL
-- Step 2) Run the file DIR.SQL by giving START DIR (or) @ DIR at SQL *Plus prompt
-- i.e. SQL> @ DIR
-- Author : Pasupuleti Sailaja, ORACLE favorite, Hyderabad-500072, India.
-- E-mail : SAILAJAMAIL@YAHOO.COM
-- Program :
set serveroutput on size 100000 feedback off
declare
rs integer;
cur integer;
rp integer;
trs integer;
n integer;
un varchar2(30);
begin
dbms_output.put_line(rpad('Table Name',40)||' Number of Rows Created Date');
dbms_output.put_line(rpad('-',73,'-'));
cur:= dbms_sql.open_cursor;
for t in (select object_name, created from user_objects where object_type='TABLE') loop
dbms_sql.parse(cur,'select count(*) from ' || t.object_name, dbms_sql.v7);
dbms_sql.define_column(cur, 1, rs);
rp:= dbms_sql.execute(cur);
n:=dbms_sql.fetch_rows(cur);
dbms_sql.column_value(cur, 1, rs);
dbms_output.put_line(rpad(t.object_name,48,'.')||rpad(rs,15,'.')||t.created);
end loop;
dbms_sql.close_cursor(cur);
select count(*) into n from tab where tabtype='TABLE';
select user into un from dual;
dbms_output.put_line(rpad('-',73,'-'));
dbms_output.put_line(un||' User contain '||n||' Table(s)');
end;
/
set serveroutput off feedback on feedback 6
-- Example Output:
Table Name Number of Rows Created Date
-------------------------------------------------------------------------
ACCTS...........................................5..............29-JUN-03
ACCT_ADDRS......................................5..............29-JUN-03
BONUS...........................................0..............09-AUG-00
CHESS_SAVE......................................0..............29-JUN-03
CHESS_SAVE_BOARDSTATE...........................0..............29-JUN-03
CHESS_SAVE_CAPTURED.............................0..............29-JUN-03
CHESS_SAVE_PLAYER...............................0..............29-JUN-03
CITIES..........................................205............29-JUN-03
COMPANY_SUMMARY.................................3..............29-JUN-03
CUSTOMER........................................9..............09-AUG-00
CUSTOMERS.......................................14.............29-JUN-03
DEMOKIT_CLASSES.................................9..............29-JUN-03
DEMOKIT_DEMOS...................................23.............29-JUN-03
DEMOKIT_DEMO_SCRIPTS............................14.............29-JUN-03
DEMOKIT_INSTALLED_SCRIPTS.......................5..............29-JUN-03
DEMOKIT_SCRIPTS.................................10.............29-JUN-03
DEPT............................................4..............09-AUG-00
DUMMY...........................................1..............09-AUG-00
DUPEMP..........................................7168...........12-AUG-00
EMP.............................................14.............09-AUG-00
FFI$FUNCTION....................................2..............29-JUN-03
FFI$FUNCTIONARGS................................10.............29-JUN-03
FFI$LIBRARY.....................................1..............29-JUN-03
FFI$TYPEDEF.....................................14.............29-JUN-03
FUNDS...........................................5..............29-JUN-03
FUND_CONTRIB....................................16.............29-JUN-03
FUND_XACT.......................................45.............29-JUN-03
F_EMPCOMP.......................................2..............29-JUN-03
F_XACT_TYPE.....................................7..............29-JUN-03
GAME_SEMAPHORE..................................0..............29-JUN-03
INDCAT..........................................11.............29-JUN-03
INVINFO.........................................5..............29-JUN-03
INVREQUEST......................................12.............29-JUN-03
ITEM............................................0..............09-AUG-00
MENU_CAT........................................7..............29-JUN-03
MENU_ITM........................................35.............29-JUN-03
MODE_BUTTON.....................................9..............29-JUN-03
ORD.............................................21.............09-AUG-00
ORDER_HISTORY...................................5..............29-JUN-03
ORDPICT.........................................21.............29-JUN-03
PORTFOLIO.......................................39.............29-JUN-03
PRICE...........................................17.............09-AUG-00
PRODUCT.........................................10.............09-AUG-00
RCL_CLASSIFICATIONS.............................6..............29-JUN-03
RCL_COMPONENTS..................................7..............29-JUN-03
RCL_COMPONENT_DEPENDANCIES......................6..............29-JUN-03
RCL_FRAGMENTS...................................31.............29-JUN-03
RCL_INSTRUCTIONS................................57.............29-JUN-03
RCL_INSTRUCTIONS_TEXT...........................520............29-JUN-03
RCL_MODULES.....................................31.............29-JUN-03
SALES_REVENUE...................................16.............29-JUN-03
SALGRADE........................................5..............09-AUG-00
STOCKS..........................................328............29-JUN-03
STOCK_HISTORY...................................11.............29-JUN-03
TABLE_CONFIG....................................11.............29-JUN-03
USA.............................................50.............29-JUN-03
-------------------------------------------------------------------------
SCOTT User contain 56 Table(s)
|
Bookmark
