question

Leon avatar image
Leon asked

Fetching table rowcount for required databases using sp_msforeachdb

Hi, I'm trying to fetch the row count of tables in a individual database by using sp_msforeachdb. However the result set returned after executing the below querry doesnot match the count from sys.objects table. create table test ( db_name varchar(100), table_name varchar(1000), row_count int) insert into test exec sp_msforeachdb 'USE [TESTDB]; select ''TESTDB'' as database_name,o.name,max(i.rowcnt ) From [TESTDB].sys.objects o inner join [?].sys.sysindexes i on o.object_id= i.id where o.type=''U'' group by o.name' -----------returns me 951 records USE TESTDB SELECT count(*) from sys.objects where type = 'U' ---------------returns me 187 records Can anyone suggest what am I doing wrong. Thanks!!!
databaserowcount
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

This site runs off of votes. Please mark all helpful answers by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate that by clicking on the check mark next to that answer.
0 Likes 0 ·
ThomasRushton avatar image
ThomasRushton answered
A couple of things: * You may have multiple indexes on tables. <= oops, my mistake. You've got that covered. Sorry! * I'm not sure about the `FROM TestDB.sys.objects INNER JOIN [?].sys.sysindexes` bit. Shouldn't you be joining `[?].sys.objects`
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I thought that about the multiple indexes, as my eyes couldn't 'see' the query in the quotes - but that's handled by the max(i.rowcnt) and group by.
1 Like 1 ·
Good point. And, guess what? I can't put a strikethrough on that line of my answer...
0 Likes 0 ·
Kev Riley avatar image
Kev Riley answered
I can see 2 problems with this query. 1 - you are using sp_msforeachdb, but then querying TESTDB.sys.objects 2 - If you have tables with the same name but in different schemas, the the first query ignores the schema If you correct those are you still getting different counts?
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
Like others have said, your script hits a mix of TestDB and each DB. My five cents: I wouldn't use sp_MSForeachDB. I have seen scenarios where sp_MSForEachDB isn't giving reliable results (skipping databases) and I haven't been able to figure out under which circumstances it doesn't work. I would instead use a query against sys.databases and use that to create dynamic SQL. Something like this: CREATE TABLE #t (db_name sysname, table_name sysname,row_count int); DECLARE @s nvarchar(max)='' SELECT @s=@s + ' INSERT INTO #t(db_name,table_name,row_count) SELECT ''' + quotename(d.name) + ''' as databasename, o.name,max(i.rowcnt) FROM ' + quotename(d.name) + '.sys.objects o INNER JOIN ' + quotename(d.name) + '.sys.sysindexes i ON o.object_id = i.id WHERE o.type=''U'' GROUP BY o.name ' FROM sys.databases d exec sp_executesql @s SELECT * from #t DROP TABLE #t I would also probably be more consistent about which system objects I'd use. Instead of sys.objects, one could use sys.tables, and instead of sys.sysindexes, one could use sys.indexes in combination with sys.partitions. That's because sys.sysindexes is only there for backward compatibility only, and doesn't fully support for example partitioned indexes. But my example above uses the same system objects as you have used in your query. Changing it to use sys.tables, sys.indexes and sys.partitions wouldn't be that big a thing, except one would have to keep in mind that partitioned indexes would have to be summed up in order to get the correct rowcount.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.