October 13, 2006 at 11:59 am
Hi all,
I am running the below query and I need to the table row counts for EACH database on the server but I can't seem to make the database name not REPEAT for each table:
SELECT DISTINCT
--[servername] = @@servername,
[DatabaseName] = sd.name,
[TableName] = so.name,
[RowCount] = MAX(si.rows)
FROM
sysobjects so,
sysindexes si,
master..sysdatabases sd
WHERE
so.xtype = 'U'
AND
si.id = OBJECT_ID(so.name)
GROUP BY
sd.name, so.name
ORDER BY
1,2, 3 DESC
ie...
Servername
Database Name1
TableName = rows
TableName = rows
Database Name2
TableName = rows
TableName = rows
etc...
Can anyone help with this coding?
Thanks...Michelle
October 13, 2006 at 4:20 pm
1) What for?
2) If there is no database name what will tell you which database this particular table belongs to?
_____________
Code for TallyGenerator
October 13, 2006 at 4:33 pm
what I mean is, i am getting back the SAME TABLES list for each database but these tables are not actually in the database. how do i make the query list only the tables that are actually in the database???
October 13, 2006 at 5:20 pm
I see a join from sysindexes to sysobjects: si.id = OBJECT_ID(so.name)
BTW, quite, mmm..., not reasonable. It must be just si.id = so.id.
But where is your join to sysdatabases?
_____________
Code for TallyGenerator
November 1, 2006 at 9:40 pm
Sysobjects - sysindexes joins on 'id'...which join should I use to include sysdatabases?
I still have not been able to get the correct results..I need the follow:
Dbname, Tblname, rowcnt
November 2, 2006 at 5:20 am
I think you should not include sysdatabases. Why would you do it?
Sysobjects and sysindexes are in each database; that means, your query always works only with tables from the CURRENT database. If I understood what you are trying to do, this could be what you need:
SELECT
db_name() [DatabaseName],
so.name [TableName],
si.rows [RowCount]
FROM sysobjects so
JOIN sysindexes si ON si.id = so.id
WHERE so.xtype = 'U'
AND si.indid IN (0,255) /*see BOL - 0 and 255 mean that it is a table (not index)*/
ORDER BY so.name DESC
This way there will be no duplicates, unless you have several tables with same name but different owner. What do you want to do if this happens?
There is undocumented procedure sp_MSforeachdb that could help you if you want to run the same code in all databases. However, I never used it myself so I can't explain how to do it properly.
November 2, 2006 at 7:42 am
Thank you very much...
Regards...Michelle
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply