How to query a database for empty tables

Question

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.

Answer 1

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.

Answer 2

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:

Answer 3

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
}

Answer 4

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

Answer 5

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;

Answer 6

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.

Answer 7

DECLARE @toCheck INT;
DECLARE @countoftables INT;
DECLARE @Qry NVARCHAR(100);
DECLARE @name VARCHAR(100);
BEGIN
    IF object_id('TEMPDB.DBO.#temp') IS NOT NULL drop table #temp;
    SELECT ROW_NUMBER() OVER(ORDER BY name) AS ROW,CountStatement = 'SELECT @toCheck = COUNT(*) FROM  ' + name,name INTO #temp FROM SYS.TABLES  WITH (NOLOCK)
    --SELECT * FROM #temp  ORDER BY ROW
    SET @countoftables  =(SELECT COUNT(*) FROM #temp)
    WHILE (@countoftables > 0)
        BEGIN
            SET @Qry =  (SELECT CountStatement FROM #temp  WITH (NOLOCK) WHERE ROW = @countoftables);
            SET @name = (SELECT name FROM #temp  WITH (NOLOCK) WHERE ROW = @countoftables);
            EXEC SP_EXECUTESQL @qry,N'@toCheck INT OUTPUT',@toCheck OUTPUT;
            IF(@toCheck=0)
                BEGIN
                    PRINT 'Table: ' + @name + ', count: ' +  convert(varchar(10),@toCheck);
                END
            --ELSE
            --  BEGIN
            --      PRINT 'Table: ' + @name + ', count: ' +  convert(varchar(10),@toCheck);
            --  END
            SET  @countoftables = @countoftables -1;            
        END
END

Answer 8

SELECT      T.name [Table Name],i.Rows [Number Of Rows]
FROM        sys.tables T
JOIN        sys.sysindexes I ON T.OBJECT_ID = I.ID
WHERE       indid IN (0,1) AND i.Rows<1
ORDER BY    i.Rows DESC,T.name