SQL Server: Retrieving the number of rows in all tables in a database

3/18/2008 10:22:14 AM #

really good ,it helps me alot

ran |

4/26/2008 9:17:36 AM #

very nice work.

Muhammad Azim |

4/28/2008 11:51:29 AM #

Do you know of a similar trick for oracle too by any chance?

Dave |

10/17/2008 12:00:24 PM #

Hey Dave! for oracle, why don't you use the following code.
----------
BEGIN
        EXECUTE IMMEDIATE 'DROP TABLE zzz_tblCount PURGE'; EXCEPTION WHEN OTHERS THEN NULL;
END;
/
--------------------------------------------------------------------------------
CREATE TABLE zzz_tblCount
(
  TBLNAME VARCHAR2(30),
  CROWS VARCHAR2(10)
);
-------------------------------------
DECLARE
  CURSOR MON_CURSOR IS  SELECT TNAME TABLENAME  FROM TAB WHERE TNAME NOT LIKE '%BIN%';
BEGIN
        FOR a IN MON_CURSOR LOOP

        BEGIN
        EXECUTE IMMEDIATE
        'INSERT INTO /*+ parallel(A,10) */  zzz_tblCount A
        (
    TBLNAME,
    CROWS
    )
  SELECT
  '''||a.TABLENAME||''',
  count(*)
  
  FROM
        '|| a.TABLENAME||'  a';
  END;
END LOOP;
END;
/
--------
select * from zzz_tblCount;

Mahesh Acharya |