sp_MSforeachdb

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.