6

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"

8
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;
  • 1
    Cool, I hadn't used QUOTENAME before. Very useful here. And fun to see a different approach to the problem. – Geoff Patterson Sep 2 '15 at 19:35
4

Below code will help you :

-- Ref: http://stackoverflow.com/a/7892349/1387418
-- for XML Path technique learned from Michael Eriksson : http://dba.stackexchange.com/a/61855/8783
set nocount on
declare @SQL nvarchar(max)
declare @tableName  sysname = N'yourTableNameGoesHere'  --- CHANGE HERE !!
declare @schemaName sysname = N'dbo'------------------------- CHANGE HERE !!

set @SQL = (select N' union all  
select '''+D.name+'''                               AS DatabaseName, 
    s.Name collate database_default                 AS SchemaName,
    t.NAME collate database_default                 AS TableName,
    p.rows                                          AS RowCounts,
    SUM(a.total_pages) * 8                          AS TotalSpaceKB, 
    SUM(a.used_pages) * 8                           AS UsedSpaceKB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8    AS UnusedSpaceKB
FROM 
    '+quotename(D.name)+'.sys.tables t
INNER JOIN      
    '+quotename(D.name)+'.sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    '+quotename(D.name)+'.sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    '+quotename(D.name)+'.sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN 
    '+quotename(D.name)+'.sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    t.NAME NOT LIKE ''dt%'' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255
    AND s.Name = '''+@schemaName+'''
    AND t.NAME = '''+@tableName+''' 

GROUP BY 
    t.Name, s.Name, p.Rows'
from sys.databases as D
where D.database_id > 4 AND D.[state] = 0 AND D.is_read_only = 0
for xml path(''), type).value('substring((./text())[1], 13)', 'nvarchar(max)')


--print @SQL
exec (@SQL)

enter image description here

4

I have a script that was easy to adapt and may work for your needs. You'll simply need to replace dbo.yourTableName with the appropriate table (including schema name).

The script is commented, but the general summary is to loop over all databases where you have SELECT permissions, check the number of rows and size of the table (if it exists), and add a corresponding row into a #temp table. At the end, you can review all of the results by looking in this temp table.

There may be a possibility of using the undocumented ms_foreachdb procedure to make the code a bit less verbose, but that's not something I have tried or would rely upon in production.

Also, note that this approach may be slower on SQL 2014+ if you have a large number of tables. This is due to an outstanding performance bug in SQL 2014

-- Create a table to capture the outputs
IF OBJECT_ID('tempdb..#sizeByDb') IS NOT NULL
    DROP TABLE #sizeByDb
CREATE TABLE #sizeByDb (
    dbName SYSNAME NOT NULL,
    schemaTableName NVARCHAR(512) NOT NULL,
    tableRows BIGINT NULL,
    tableMb FLOAT NULL
)
GO

-- Define variables to be used in the cursor below
DECLARE @dbName SYSNAME,
        @dbId INT,
        @schemaTableName NVARCHAR(512) = N'dbo.yourTable',
        @stmtString NVARCHAR(MAX)

-- Create a cursor that loops over each database that you have permissions for and is online
DECLARE dbCursor CURSOR LOCAL FAST_FORWARD FOR
SELECT database_id, name
FROM master.sys.databases 
WHERE HAS_PERMS_BY_NAME(name, 'DATABASE', 'SELECT') = 1
    AND state = 0 -- 0 = ONLINE
    AND is_read_only = 0

OPEN dbCursor
FETCH NEXT FROM dbCursor INTO @dbId, @dbName
WHILE @@FETCH_STATUS = 0
BEGIN
    -- For each database, build a string that will compute the number of rows and size (in MB) of the table
    -- If the table does not exist on the database, we'll insert a row with NULLs
    SET @stmtString = N'
        INSERT INTO #sizeByDb (dbName, schemaTableName, tableRows, tableMb)
        SELECT @dbName AS dbName,
            @schemaTableName AS schemaTableName,
            SUM(CASE WHEN p.index_id IN (0, 1) THEN rows END) AS tableRows, 
            SUM((a.total_pages*8.0) / (1024.0)) AS tableMb
        FROM ' + QUOTENAME(@dbName) + '.sys.partitions p
        JOIN ' + QUOTENAME(@dbName) + '.sys.allocation_units a
            ON a.container_id = p.partition_id
        WHERE p.object_id = OBJECT_ID(''' + QUOTENAME(@dbName) + '.'' + @schemaTableName)
    '
    EXEC sp_executesql @stmt = @stmtString,
                        @params = N'@dbName SYSNAME, @dbId INT, @schemaTableName NVARCHAR(512)',
                        @dbName = @dbName, @dbId = @dbId, @schemaTableName = @schemaTableName

FETCH NEXT FROM dbCursor INTO @dbId, @dbName
END
CLOSE dbCursor
DEALLOCATE dbCursor
GO

-- View the table sizes
SELECT *
FROM #sizeByDb
WHERE tableRows IS NOT NULL

Here is an example of the results (with dbName and schemaTableName anonymized)

enter image description here

  • the connect link is broken - Page Not Found. – Kin Shah Sep 3 '15 at 14:58
  • @Kin Interesting, thanks for letting me know!. It works for me in both Chrome (where I was previously signed in to Connect) and Firefox (where I was not). However, I was able to find the issue via a search within Connect and that did yield a slightly different link. I'll update the answer to use that link now. – Geoff Patterson Sep 3 '15 at 15:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.