Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Due to some 'developers' we had working on our system we have had issues with empty tables. We have found that during the transfer to the cloud several tables were copied, but the data in them wasn't.

I would like to run a query the system tables to find what user tables are empty. We are using MS SQL 2008 R2.

Thanks for the help.

share|improve this question

7 Answers 7

Leverage sys.tables and sys.partitions:

select
    t.name table_name,
    s.name schema_name,
    sum(p.rows) total_rows
from
    sys.tables t
    join sys.schemas s on (t.schema_id = s.schema_id)
    join sys.partitions p on (t.object_id = p.object_id)
where p.index_id in (0,1)
group by t.name,s.name
having sum(p.rows) = 0;

Use a sum of rows just to make sure you don't have confusion with partitioned tables. Index_ID of 0 or 1 means you're only looking at the row counts for your heaps or clustered indexes.

share|improve this answer
    
Thanks, it does exactly what I need. –  codehammer Aug 7 '13 at 17:26
2  
Suggest adding schema to the output. –  Aaron Bertrand Aug 7 '13 at 18:05
    
@AaronBertrand Done and done. –  Mike Fal Aug 7 '13 at 18:09

As Mike Fal and Kin have both noted, the system tables are your friend.

For a more code-complete version, I've come up with the following, which would allow you to see the total data space used by each table in your database.

USE master;

CREATE DATABASE TestDB;
GO

USE tempdb;
ALTER DATABASE TestDB SET RECOVERY SIMPLE;
GO

USE TestDB;
CREATE TABLE Test1 (
    Test1ID INT NOT NULL PRIMARY KEY IDENTITY(1,1)
    , TestData nvarchar(255) CONSTRAINT DF_Test1_TestData DEFAULT (NEWID())
);

GO

TRUNCATE TABLE Test1;

SELECT s.name + '.' + t.name AS TableName,
    sum(p.rows) AS TotalRows,
    SUM(au.data_pages) AS DataPagesUsed
FROM sys.tables t
    INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
    INNER JOIN sys.partitions p ON t.object_id = p.object_id
    INNER JOIN sys.allocation_units au ON p.hobt_id = au.container_id
WHERE au.type = 1 or au.type = 3 
    AND t.is_ms_shipped = 0
GROUP BY s.name, t.name
    ORDER BY SUM(au.data_pages) DESC;

INSERT INTO Test1 DEFAULT VALUES;

SELECT s.name + '.' + t.name AS TableName,
    sum(p.rows) AS TotalRows,
    SUM(au.data_pages) AS DataPagesUsed
FROM sys.tables t
    INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
    INNER JOIN sys.partitions p ON t.object_id = p.object_id
    INNER JOIN sys.allocation_units au ON p.hobt_id = au.container_id
WHERE au.type = 1 or au.type = 3 
    AND t.is_ms_shipped = 0
GROUP BY s.name, t.name
    ORDER BY SUM(au.data_pages) DESC;

Results of the last 3 statements:

enter image description here

share|improve this answer

There are many ways to achieve the same result. Another variant is to use sys.dm_db_partition_stats with sys.indexes and sys.objects.

SELECT NAME [Table]
    ,sum(row_count) AS [Rows]
FROM sys.dm_db_partition_stats 
    ,sysobjects 
WHERE index_id < 2 -- Just grab index 0 (heap) or 1 (cluster)
    AND xtype = 'U'
    AND object_id = object_id('' + NAME + '')
GROUP BY NAME
having sum(row_count) = 0 -- since you want empty tables !!
share|improve this answer
    
This will return multiple rows for a partitioned table. It will also return multiple rows if you have two tables with the same name in different schemas. –  Aaron Bertrand Aug 7 '13 at 18:05
    
@AaronBertrand Updated the code, now it should be fine. Returns the same results as from Mike's query. –  Kin Aug 7 '13 at 18:11
2  
Why a cross join? Why the deprecated sysobjects? Why do none of your column references have aliases? Why still no schema? –  Aaron Bertrand Aug 7 '13 at 19:40

Here is a PowerShell version:

Using SQL Server Management Objects (SMO)


function Find-EmptyTables ($server,$database) 
{

    # Load SMO assembly
    [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | Out-Null

    $s = New-Object 'Microsoft.SqlServer.Management.Smo.Server' $server
    $db = $s.Databases.Item($database)
    $db.Tables | Where-Object { $_.RowCount -eq 0 } | Select Schema, Name, RowCount
}

Depending on the number of databases you can use the above function against a list of each database name populated in a variable and output it all at the same time, if dealing with one server:


$DBList = 'MyDatabase1','MyDatabase2'

foreach ($d in $DBList) {
Find-EmptyTables -server MyServer -database $d | 
  Select @{Label="Database";Expression={$d}}, Schema, Name, RowCount
}
share|improve this answer

Other responses here are great, but for completeness: SQL Server Management Studio > right-click the DB > Reports > Standard Reports > Disk Usage by Table

share|improve this answer
    
This would return all tables though, not just those that are empty. I don't believe you can apply filters to those reports. –  Shawn Melton Aug 8 '13 at 2:08
    
That is true. Pad Pad Pad –  onupdatecascade Aug 13 '13 at 2:34
    
But it's a very good answer. Just at 2 clicks distance, easier than ever. –  Marian Aug 13 '13 at 20:43

I generally just create a query that creates the query that I want and then execute that manually, but if you want it all in one go...

declare @sql nvarchar(max) ;

set @sql = ';with cte as (' + (select  
        ( 
            SELECT case when row_number() 
                 over (order by table_schema, table_name) = 1 then '       ' 
                   else ' union ' end + 
                'select count(*) rws, ''[' +
                      t.TABLE_SCHEMA +'].[' + t.table_name + 
                ']'' tbl from ' + '['+ 
                      t.TABLE_SCHEMA + '].[' + TABLE_NAME + ']' + 
                CHAR(10) AS [data()] 
            FROM INFORMATION_SCHEMA.TABLES t
            FOR XML PATH ('') 
        )) + ') select * from cte where rws = 0;'

execute sp_executesql @sql;
share|improve this answer

As an additional answer, the undocumented system stored procedure sp_MSforeachtable is useful here.

CREATE TABLE #CountRows ( TableName nvarchar(260), NumRows int) ;
GO
EXEC sp_MSforeachtable 'insert into #CountRows select ''?'', count(*) from ?' ;
SELECT * FROM #CountRows WHERE NumRows = 0 ORDER BY TableName ;
DROP TABLE #CountRows ;

The usual warnings about undocumented features apply.

You can look at the source code of the procedure in master if you are curious or if you want to be certain it has no nasty side effects. It uses dynamic SQL to build a cursor, which is bad for performance (cursor=slow!), so only use this procedure for a one-off task.

Additionally, sp_MSforeachtable is not available in Azure Database.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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