sp_MSforeachdb is an undocumented system stored procedure that lets you iterate through all databases on an instance and execute a T-SQL statement. This can be a very handy tool for the DBA. For instance, let us create a script that lists all tables and their size on a server instance.
T-SQL to list all table sizes on a server
Use the following script with caution, since there may be many tables on your server:
IF OBJECT_ID('tempdb..#AllTableSizes') IS NOT NULL BEGIN DROP TABLE #AllTableSizes END CREATE TABLE #AllTableSizes ( TableName NVARCHAR(MAX) NOT NULL ,SizeInMB INT NOT NULL ) EXECUTE sp_MSforeachdb 'USE [?]; INSERT #AllTableSizes SELECT DB_NAME() + ''.'' + T2.name + ''.'' + T0.name ,SUM(T1.reserved_page_count*8.0/1024) FROM sys.objects T0 JOIN sys.dm_db_partition_stats T1 ON T0.object_id = T1.object_id JOIN sys.schemas T2 ON T0.schema_id = T2.schema_id WHERE [type] = ''U'' AND T1.index_id <= 1 GROUP BY DB_NAME() + ''.'' + T2.name + ''.'' + T0.name ' SELECT * FROM #AllTableSizes ORDER BY SizeInMB DESC
The script uses sp_MSforeachdb to iterate through all databases, and insert all tables with into the temporary table #AllTableSizes.
Another related undocumented system stored procedure is sp_MSforeachtable that iterates through all tables in a database.