SQL Server 2008 R2 – Finding Data Size of Tables Across Databases

sql serversql-server-2008-r2

I have several identical databases with the same schema/table structure within a instance. I need to find the size of one table that they all have. For example, I have 30 databases on a instance and all the databases have a "personal information" table. I need to find a way to query the size of "personal information" table that they all have, instead of going individually to each database.

Is there a way to do this?

Similar to SP_Spaceused for size "Data Column"

Best Answer

DECLARE @table  SYSNAME = N'Personal Information',
        @schema SYSNAME = N'dbo';

CREATE TABLE #sz
(
  dbname     NVARCHAR(255),
  fullname   NVARCHAR(768), [rows] SYSNAME, 
  reserved   SYSNAME,       [data] SYSNAME,
  index_size SYSNAME,       unused SYSNAME
);

DECLARE @sql NVARCHAR(MAX) = N'DECLARE @t NVARCHAR(512);';

SELECT @sql += N'IF EXISTS (SELECT 1 FROM ' + QUOTENAME(name)
    + N'.sys.tables AS t INNER JOIN ' + QUOTENAME(name)
    + N'.sys.schemas AS s ON t.[schema_id] = s.[schema_id]
    WHERE t.name = @table AND s.name = @schema) 
    BEGIN
      SET @t = N''' + QUOTENAME(name) + N'.'' + QUOTENAME(@schema) 
        + N''.'' + QUOTENAME(@table); INSERT #sz(fullname, [rows],
          reserved, [data], index_size, unused) EXEC ' 
          + QUOTENAME(name) + N'.sys.sp_spaceused @t; UPDATE #sz
            SET dbname = N''' + name + N''' WHERE dbname IS NULL;
    END '
FROM sys.databases 
WHERE database_id > 4 AND [state] = 0 AND is_read_only = 0;

EXEC sys.sp_executesql @sql, N'@table SYSNAME, @schema SYSNAME', @table, @schema;

SELECT [database] = dbname, 
  [schema] = @schema, [table] = @table, 
  [rows], reserved, [data], index_size, unused
FROM #sz;

DROP TABLE #sz;
Related Question