Wednesday, February 11, 2015

Oracle PL/SQL: Give me record counts for all tables in a schema

This piece of SQL will output the row counts for every table in the current schema in a CSV format;

declare 
  i integer;
begin
  dbms_output.put_line('Table Name,Row Count,' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
  for v_table in (select ut.table_name from user_tables ut) loop
    execute immediate
      'select count(*) from ' || v_Table.Table_Name
      into i;
    dbms_output.put_line(v_Table.Table_Name || ',' || TO_CHAR(i));
  end loop;

end;

Very useful for doing before/ after migration comparisons.