Select tables where any data exists

Question

I have acces to huge MSSQL DB. This DB have many tables, but huge amount of it is empty. How do I query DB schema to select table names, where any rows exists? (I'd like to create ERD only from these tables that haves some data, when I achieve this). I did not found any related questions.

*"I did not found any related questions"* [How to fetch the row count for all tables in a SQL SERVER database](https://stackoverflow.com/questions/2221555/how-to-fetch-the-row-count-for-all-tables-in-a-sql-server-database/19632515). — Thom A, Oct 22 '19 at 11:43
Possible duplicate of [How to fetch the row count for all tables in a SQL SERVER database](https://stackoverflow.com/questions/2221555/how-to-fetch-the-row-count-for-all-tables-in-a-sql-server-database) — Amira Bedhiafi, Oct 22 '19 at 11:52

score 6 · Answer 1 · answered Oct 22 '19 at 11:27

A quick but approximate query you can use is the following one, just check the RowCount column:

SELECT 
    TableName = t.NAME,
    SchemaName = s.Name,
    [RowCount] = p.rows,
    TotalSpaceMB = CONVERT(DECIMAL(18,2), SUM(a.total_pages) * 8 / 1024.0), 
    UsedSpaceMB = CONVERT(DECIMAL(18,2), SUM(a.used_pages) * 8 / 1024.0),
    UnusedSpaceMB = CONVERT(DECIMAL(18,2), (SUM(a.total_pages) - SUM(a.used_pages)) * 8 / 1024.0)
FROM 
    sys.tables t
    INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
    INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
    INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
    LEFT OUTER JOIN 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 
GROUP BY 
    t.Name, 
    s.Name, 
    p.Rows
ORDER BY 
    [RowCount] DESC

If you want the real count, you will have to issue a SELECT that returns a script of multiple SELECT with COUNT(*) and probably a bunch of UNION ALL. It might take long to finish if you are concurrently accessing the tables or if they are very big.

score 2 · Answer 2 · answered Oct 22 '19 at 11:34

If you do a real count then you can use a dynamic script to do this. Note that, as @Ezlo mentions, this will be (a lot) slower than the estimated counts using the sys.partitions object:

DECLARE @SQL nvarchar(MAX),
        @CRLF nchar(2) = NCHAR(13) + NCHAR(10);

SET @SQL = STUFF((SELECT @CRLF + 
                         N'UNION ALL' + @CRLF + 
                         N'SELECT N' + QUOTENAME(s.[name],'''') + N' AS SchemaName,' + @CRLF + 
                         N'       N' + QUOTENAME(t.[name],'''') + N' AS TableName,' + @CRLF +
                         N'       COUNT(*) AS TotalRows' + @CRLF +
                         N'FROM ' + QUOTENAME(s.[name]) + N'.' + QUOTENAME(t.[name])
                  FROM sys.schemas s
                       JOIN sys.tables t ON s.schema_id = t.schema_id
                  FOR XML PATH(''),TYPE).value('.','nvarchar(MAX)'),1,11,N'') + N';'

--SELECT @SQL; --To see the SQL if you want
EXEC sp_executesql @SQL;

Select tables where any data exists

2 Answers2