|
-
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!
-
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.
-
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.
-
 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?
-
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
-
Uhm... how about creating a Procedure?
it can be easier?
-
easier than what? - what are you actually trying to do because it is a stupid requirement
-
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
-
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.
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|
Reply With Quote
