-1

The question is simple:

Is it possible to retrive all the tables that are not empty ?

I need a query to list the tables. Is there a way ?

Thanks to support

Pasindu
  • 340
  • 1
  • 8
  • 24
DarioN1
  • 2,460
  • 7
  • 32
  • 67
  • 1
    have you had a look into https://stackoverflow.com/questions/17748417/sql-server-management-studio-finding-all-non-empty-tables or https://dba.stackexchange.com/questions/47726/how-to-query-a-database-for-empty-tables – Stephen May 22 '18 at 12:34
  • possible duplicate - https://stackoverflow.com/questions/3980622/sql-server-2008-i-have-1000-tables-i-need-to-know-which-tables-have-data?utm_medium=organic&utm_source=google_rich_qa&utm_campaign=google_rich_qa – Abhishek May 22 '18 at 12:35
  • 1
    Possible duplicate of [SQL Server Management Studio - Finding all non empty tables](https://stackoverflow.com/questions/17748417/sql-server-management-studio-finding-all-non-empty-tables) – DarioN1 May 22 '18 at 12:35

2 Answers2

3

Try this Script To get all tables with non empty records

    USE [Your database Name]
    Go
    SELECT SCHEMA_NAME(schema_id) AS [SchemaName],
            [Tables].name AS [TableName]
            --SUM([Partitions].[rows]) AS [TotalRowCount]
    FROM sys.tables AS [Tables]
    JOIN sys.partitions AS [Partitions]
        ON [Tables].[object_id] = [Partitions].[object_id]
        AND [Partitions].index_id IN ( 0, 1 )
    -- WHERE [Tables].name = N'name of the table'
    GROUP BY SCHEMA_NAME(schema_id), [Tables].name
    HAVING SUM([Partitions].[rows]) >0
Sreenu131
  • 2,476
  • 1
  • 7
  • 18
1

Slightly different than @Sreenu131 answer as it is using sys.partitions .rows property to find

p.rows > 0

SELECT 
    sch.name as SchemaName,
    t.NAME AS TableName,
    p.rows AS RowCounts
FROM 
    sys.tables t
INNER JOIN 
    sys.partitions p ON t.object_id = p.OBJECT_ID 
INNER JOIN sys.schemas sch
    on t.schema_id = sch.schema_id
WHERE 
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND p.rows > 0
GROUP BY 
    sch.name,t.Name, p.Rows
ORDER BY 
    sch.name,t.Name
jitin14
  • 144
  • 5