MS SQL: List All Tables in All Databases and File Sizes

-- Create a temporary table to store results
CREATE TABLE #TableSizes (
    DatabaseName NVARCHAR(128),
    TableName NVARCHAR(128),
    RowCounts INT,
    TotalSpaceMB DECIMAL(18,2),
    UsedSpaceMB DECIMAL(18,2),
    UnusedSpaceMB DECIMAL(18,2)
);

-- Cursor to loop through all databases
DECLARE @DatabaseName NVARCHAR(128);
DECLARE db_cursor CURSOR FOR
SELECT name FROM sys.databases
WHERE state_desc = 'ONLINE' AND name NOT IN ('master', 'tempdb', 'model', 'msdb');

OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @DatabaseName;

WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @SQL NVARCHAR(MAX);
    SET @SQL = '
    USE [' + @DatabaseName + '];
    INSERT INTO #TableSizes
    SELECT
        ''' + @DatabaseName + ''' AS DatabaseName,
        t.NAME AS TableName,
        p.rows AS RowCounts,
        CAST(SUM(a.total_pages) * 8 / 1024.0 AS DECIMAL(18,2)) AS TotalSpaceMB,
        CAST(SUM(a.used_pages) * 8 / 1024.0 AS DECIMAL(18,2)) AS UsedSpaceMB,
        CAST((SUM(a.total_pages) - SUM(a.used_pages)) * 8 / 1024.0 AS DECIMAL(18,2)) AS UnusedSpaceMB
    FROM
        sys.tables t
    INNER JOIN
        sys.indexes i ON t.OBJECT_ID = i.object_id
    INNER JOIN
        sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
    INNER JOIN
        sys.allocation_units a ON p.partition_id = a.container_id
    WHERE
        t.is_ms_shipped = 0
    GROUP BY
        t.NAME, p.rows;
    ';
    EXEC sp_executesql @SQL;
    FETCH NEXT FROM db_cursor INTO @DatabaseName;
END
CLOSE db_cursor;
DEALLOCATE db_cursor;

-- Final result
SELECT * FROM #TableSizes ORDER BY DatabaseName, TotalSpaceMB DESC;

-- Clean up
DROP TABLE #TableSizes;