Query table size from all databases

Question

I am currently a JR. DBA and a huge noob at SQL Server.

I have one table that is present in most of my databases and I need to find a way to query the size of that table from all the databases that contain it.

ex:

dbA Table1 Size dbB Table1 Size etc.

I have found multiple queries that will give me the table from one database but not all. I have also found a query that will gather the table present in all databases but does not give me the size.

Below is one I found that lists the DBName, the specific table I need, but no size.

SET NOCOUNT ON
DECLARE @AllTables table (DbName sysname,SchemaName sysname, TableName sysname)
DECLARE
     @SearchDb nvarchar(200)
    ,@SearchSchema nvarchar(200)
    ,@SearchTable nvarchar(200)
    ,@SQL nvarchar(4000)
SET @SearchDb='%'
SET @SearchSchema='%'
SET @SearchTable='%docs%'
SET @SQL='select ''?'' as DbName, s.name as SchemaName, t.name as TableName from [?].sys.tables t inner join sys.schemas s on t.schema_id=s.schema_id WHERE ''?'' LIKE '''+@SearchDb+''' AND s.name LIKE '''+@SearchSchema+''' AND t.name LIKE '''+@SearchTable+''''


INSERT INTO @AllTables (DbName, SchemaName, TableName)
    EXEC sp_msforeachdb @SQL
SET NOCOUNT OFF
SELECT * FROM @AllTables ORDER BY DbName, SchemaName, TableName

Can someone please help me with how I can get the size of the table in there?

Thank you!

You need @marc_s' answer here http://stackoverflow.com/a/7892349/2589202 — crthompson, Apr 09 '14 at 22:22
That one I saw and it only gets the tables for one database, not all of them...unless i'm missing something. — user3517246, Apr 10 '14 at 22:15
you already loop through each database with the sp_foreachdb.. his is just the sql that you would use in your @SQL variable. — crthompson, Apr 10 '14 at 22:26

score 0 · Answer 1 · answered Apr 09 '14 at 23:33

Quick and dirty with powershell:

$s = new-object microsoft.sqlserver.management.smo.server "your_server_here";

foreach ($db in $s.Databases) {
   foreach ($table in $db.Tables) {
      $table | select @{name="db";expression={$db.name}}, name, dataspaceused
   }
}

Size is listed in Kb.

Query table size from all databases

1 Answers1