You must Sign In to post a response.
  • Category: .NET

    Count(*) from n tables


    Friends , My doubt how to get the count(*) from n tables in oracle 8i.

    and i want to display this result in excel on daily basis. can any one help me on this.

  • #764453
    Hi Mahesh,

    1) Using SQL you can achieve this, try following query:
    CREATE TABLE #TableRowCounts
    TableName varchar(300),
    FinalRowCount int
    EXEC sp_MSForEachTable @command1='INSERT ##TableRowCounts (TableName, FinalRowCount) SELECT ''?'', COUNT(*) FROM ?'
    SELECT TableName, FinalRowCount FROM #TableRowCounts ORDER BY TableName, FinalRowCount DESC
    2) Actually I don't know much about oracle 8i, still I think you can get your answer using oracle 8i by referring following URL:
    Hope it helps.
    Shashikant Gurav

  • #764465
    You can try some query and get the result
    Following is the sample query. But its SQL Server. You can try the same for oracle

    SELECT +'.'+ TableName
    ,SUM(pa.rows) RowCnt
    FROM sys.tables ta
    INNER JOIN sys.partitions pa
    INNER JOIN sys.schemas sc
    ON ta.schema_id = sc.schema_id
    WHERE ta.is_ms_shipped = 0 AND pa.index_id IN (1,0)
    ORDER BY SUM(pa.rows) DESC

    1. you can directly export from the result.
    2. If your are using the c# code, you can go for ADO.NET code and export to the excel

    By Nathan
    Direction is important than speed

  • #764474
    Run below oracle script in editor, it will help you more in order to resolve issue

    Set heading off
    Set feedback off
    Set pagesize 0
    Set termout off
    Set trimout on
    Set trimspool on
    Set recsep off
    Set linesize 100
    Column d noprint new_value date_
    Column u noprint new_value user_
    Spool tmp
    Select 'Select '''||table_name||' : ''||count(*) from '||table_name||';',
    to_char(sysdate, 'YYYYMMDDHH24MISS') d, user u
    from user_tables
    order by table_name
    Spool off
    Spool count_&user_._&date_
    Spool off

    Editor, DotNetSpider MVM
    Microsoft MVP 2014 [ASP.NET/IIS]

  • #764519
    I recommended using following code snippet to Count(*) from n tables . you need to change table mane according to your data
    $result=mysql_query("SELECT count(*) as total from Example");
    echo $data['total'];

  • #764572
    Hello Mahesh,

    You can use a dataset concept.

    dim sql as string
    sql="select count(id) from tablename"

    store the output in dataset and export that dataset in excel using the required .dlls.

    Inform me if you still not table to get the output.

    Dhiraj C. Solanki

  • Sign In to post your comments