Oracle database queries the names of all tables and the number of records in the statistics table

Start by querying the owner,table_name,comments (description, annotations) of all tables, where owner is the category you want to query

　　

Select tcom. OWNER, tcom. Table_name,tcom. COMMENTS from   all_tab_comments tcom  where   owner=' Sadmin '  and Table_type='table';

To count the number of records per table, you can make a nested query, first selecting all the names of the tables, and then counting the number of bars in the count record. Simply say the questions you will encounter:

1. When the inner loop is in progress, the resultset inside will be next ().

2. If the amount of data is too large, the inner loop will report that the database cursor has used the maximum error. At this time, the inner loop should be pumped into a method, in the corresponding method inside the query, and after the query is complete, the resultset and PreparedStatement closed.

String sql = "*********"; PDST=conn.preparestatement (SQL); //The results of the names and descriptions of all tablesresult =Pdst.executequery (); String Total; //number of record bars per table                         while(Result.next ()) {//the name of the table to queryString tablename=result.getstring ("OWNER") + "." +result.getstring ("table_name"); String Sql_num= "SELECT count (*) from" +TableName; Call method to avoid the presence of cursors not enough= ExecuteSQL (conn, Sql_num, "count (*)"); }        } Catch(Exception e) {e.printstacktrace (); }finally{closeall (conn, pdst, result); }         ///////////////////////////////////////////////////////////////*** Execute the SQL statements inside the inner loop to avoid the presence of cursors that are not sufficient *@paramcon connection to the database *@paramSQL SQL statements *@paramgetstr fields in the data table for the data to be returned *@returnThe value of the corresponding field in the query result*/     Public StaticString ExecuteSQL (Connection con,string sql,string getstr) {//Query MethodPreparedStatement PS =NULL; ResultSet RESULT1=NULL; String Str=NULL; Try{PS=con.preparestatement (SQL); RESULT1=Ps.executequery (); if(Result1.next ()) {str=result1.getstring (GETSTR); }                } Catch(SQLException e) {e.printstacktrace (); } finally {            if(PS! =NULL){                Try{ps.close (); } Catch(SQLException e) {e.printstacktrace (); }            }            if(RESULT1! =NULL){                Try{result1.close (); } Catch(SQLException e) {e.printstacktrace (); }            }        }        returnstr; }               

Oracle database queries the names of all tables and the number of records in the statistics table