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!!!
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`
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?
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.