August 25, 2016 at 8:44 am
Hi
Is there a single query that will list the table row counts for all the databases in an instance?
August 25, 2016 at 8:51 am
No, but you can query sys.partitions to get the rows per table in a database, and use something like sp_MSForEachDB to loop through all databases and execute the same query.
John
August 25, 2016 at 8:54 am
As each database is a container on itself, you need to loop into every database. One easy way to do it is with the sp_foreachdb shared in here: https://www.mssqltips.com/sqlservertip/2201/making-a-more-reliable-and-flexible-spmsforeachdb/
There's one included which is undocumented, but it might have some problems.
Then you just insert your results from the following query into a table to get the row counts of every table:
SELECT DB_NAME() DBName,
OBJECT_NAME(object_id) AS tableName,
SUM(row_count) Row_Count
FROM sys.dm_db_partition_stats
WHERE index_id IN (0,1)
GROUP BY object_id;
NOTE: Don't forget to include the schema if needed.
August 25, 2016 at 9:25 am
Another alternative is to construct a dynamic UNION ALL query by SELECTing from sys.databases.
Just a quick example of how that might look if my goal is to see all tables ordered by row count:
DECLARE @sql NVARCHAR(max)='';
SELECT @sql=@sql+' UNION ALL
SELECT database_name='''+QUOTENAME(name)+''',
schema_name=s.name,
table_name=t.name,
row_count=SUM(p.rows)
FROM '+QUOTENAME(name)+'.sys.partitions p
INNER JOIN
'+QUOTENAME(name)+'.sys.tables t ON p.object_id=t.object_id
INNER JOIN
'+QUOTENAME(name)+'.sys.schemas s ON t.schema_id=s.schema_id
WHERE p.index_id IN (0,1)
GROUP BY t.name,
s.name
'
FROM sys.databases
WHERE state_desc='ONLINE';
SET @sql=STUFF(@sql,1,10,'')+' ORDER BY row_count DESC;';
EXEC (@sql);
Cheers!
August 26, 2016 at 7:09 am
Thanks for the help guys. Check out this blog which gave me what i needed.
http://blogs.lessthandot.com/index.php/datamgmt/datadesign/how-to-get-information-about-all-databas/
August 26, 2016 at 7:24 am
jdbrown239 (8/26/2016)
Read the article that I mentioned before, here's an extract to show why it's important:
I have discovered instances where, under heavy load and/or with a large number of databases, the procedure can actually skip multiple catalogs with no error or warning message
August 26, 2016 at 10:06 am
OK will do.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply