October 27, 2008 at 8:40 am
I am trying to develop script for getting table names, row count for each table. I want to do do it for each database. But I am not sure where I can find information.
I can use db_name() to get current DB. But I want to the build query to loop through each db.
May be, there is some system view from which I can take information to join. not sure about it
I am trying to get following result set:
DB-name, table-name, table-owner,row counts
Please suggest...
Thanks
October 27, 2008 at 8:55 am
this has been the fastest way I've found, because it's using index tables to count rows, rather than actually running a statemtn.
that's important to note, because if you have a heap table with no index, it will show no rows, and wouldn't be accurate.
stick this procedure in MASTER, and simply use the msForEach below:
ALTER PROCEDURE sp__CountTableRows
AS
BEGIN
SELECT db_name(),sysobjects.name, MAX(sysindexes.rows) AS NUMROWS
FROM sysobjects
INNER JOIN sysindexes ON sysobjects.id = sysindexes.id
WHERE sysobjects.xtype = 'U'
GROUP by sysobjects.name
ORDER BY NUMROWS DESC,sysobjects.name
END
GO
sp_msforeachdb 'USE ? exec sp__CountTableRows'
Lowell
October 27, 2008 at 8:57 am
Okay this is similar to Lowell's solution, but I'll put it out there anyway:
sp_msforeachdb @command1 = 'Select
''?'' as db,
S.name as schemaname,
T.name as tablename,
P.rows
from
sys.partitions P Join
sys.tables T On
P.object_Id = T.object_id Join
sys.schemas S On
T.schema_id = S.schema_id
Where
P.index_id in (0,1)'
The Where clause takes the row count only from the HEAP (0) or Clustered Index (1).
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 27, 2008 at 10:32 am
hi there,
I am getting each DB name in result. but not the table names.
It shows me name of only those tables against which I am running query. For example: if the query is run against Master DB, though I get different Db name, I get same tables for each Db set...
October 27, 2008 at 11:04 am
you need to make sure your msForEach has a USE statement:
sp_msforeachdb 'USE ? exec sp__CountTableRows'
Jack's Script needed to explicitly use the ? variable for the dbname"
sp_msforeachdb @command1 = 'Select
''?'' as db,
S.name as schemaname,
T.name as tablename,
P.rows
from
?.sys.partitions P Join
?.sys.tables T On
P.object_Id = T.object_id Join
?.sys.schemas S On
T.schema_id = S.schema_id
Where
P.index_id in (0,1)'
Lowell
October 27, 2008 at 11:17 am
gotcha!
October 27, 2008 at 1:57 pm
Lowell,
Thanks for fixing my code too! I always forget that one because I always think it changes the context, but it doesn't.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 27, 2008 at 2:03 pm
i know what you mean;
I wrote this neat function that returns the DDL of a table, along with it's indexes....you know, CREATE TABLE.....
works fine in any database, but of course I wanted to just stick it in MASTER and be done with it...but when called from anotehr db, it would either not find the table passed to the proc, or return teh table in the MASTER table, and not the db i was calling from.
ended up making it a procedure , which has no problems with references like that, but I fiddled with it for a long time, lost in guessing how to make the function work;
Lowell
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply