Count ALL rows in databases
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: Count ALL rows in databases

  1. #1
    Join Date
    Mar 2008
    Posts
    20

    Count ALL rows in databases

    Hi experts!
    I need the total number of ROWS in EACH database i have (All schema)

    I've tried this

    select sum(num_rows)
    from sys.dba_tables
    where owner like '%%'

    i obtain the number of all rows for all schema in database.
    I have many database, i use agent g10.0 and enterprice manager to query them.
    I'm in Ent manager repository EMREP. How can i group by EACH database name and its rows?

    Thanx!

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,001
    Why do you care how many rows are in each database. The query that you show is meaningless. If you are having performance problems you need to do some basic sql tuning. If you are just looking to manage your databases, there are better metrics that would tell you the general health of a database. you might start with the alert log.
    this space intentionally left blank

  3. #3
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    That's the more weird business requirement I ever heard; by the way, your query is not counting rows, your query is counting how many rows these tables had the last time they got analyzed.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  4. #4
    Join Date
    Mar 2008
    Posts
    20
    Quote Originally Posted by PAVB
    That's the more weird business requirement I ever heard; by the way, your query is not counting rows, your query is counting how many rows these tables had the last time they got analyzed.
    are you sure?
    sys.dba_tables report all rows that were analyzed? uhm....

    Do u have a solution? could u help me please?

  5. #5
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,477

    Cool

    Quote Originally Posted by master.jimmy
    Do u have a solution? could u help me please?
    It's not only weird requirement as PAVB points out, but an exercise in futility as gandolf989 points out.

    Knowing the number of rows for each and all tables does not provide any useful information.

    Anyway, analyze all schemas. Then use your query removing the condition (where) and adding group-by of the SUM() by owner to get the result we 'think' you are looking for.
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  6. #6
    Join Date
    Mar 2008
    Posts
    20
    Uhm... how about creating a Procedure?
    it can be easier?

  7. #7
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    easier than what? - what are you actually trying to do because it is a stupid requirement

  8. #8
    Join Date
    Mar 2008
    Posts
    20
    i dunno know what can i do. I can create a file to execute and it will do:
    - connection to a Database
    - counting how many rows these tables had the last time they got analyzed
    - write this value on a table

  9. #9
    Join Date
    Mar 2008
    Posts
    20
    Ok, perhaps i found a better solution.

    In Enterprise Manager, when you select a Database Istance, then go on "administration" tab, and u click on TABLES link, you can see number of rows and last time the table was analyzed. How are they have been calculated? With table dba_tables? How can i query to obtain these values?

    easier than what? - what are you actually trying to do because it is a stupid requirement
    I know, but my boss wants this info :(
    Last edited by master.jimmy; 06-09-2008 at 04:51 AM.

  10. #10
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Still circling around this?

    Oh boy, you and your boss, what a pair. There is an old saying in Occitan, better saying in Provençal that could be translated to something like "... God creates them, then the wind puts them together."
    Last edited by PAVB; 06-09-2008 at 09:06 AM.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width