1

I want to query how to find all the empty tables in my schema HR i already tried with select * from dba_tables where owner='HR' and num-rows is null; but i dont know if this is the correct logic or not

  • select count(*) from ... will NEVER return NULL, it returns 0 if there are 0 rows – sepupic Oct 26 '17 at 12:44
  • in this way i have to directly give the table name that is select count(*) from A; but suppose i dont know the table name and there are mnay tables under schema HR and i have to write a query where i can find all the tables which doesnt have any row that is empty table only in the schema hr – aamir Oct 26 '17 at 12:55
  • >>>n this way i have to directly give the table name that is select count(*) from A; <<< the key moment is not a table name, but the condition: rowcount IS NEVER NULL, IT CAN BE 0 BUT NOT NULL – sepupic Oct 26 '17 at 12:58
  • Although you tagged your question with Microsoft SQL Server tags, DBA_TABLES is an Oracle view. Please correct the tags as needed for appropriate answers. – Dan Guzman Oct 26 '17 at 14:32
5

When you say 'empty' tables, I take that to mean tables with zero rows.

Using the example in this post as a starting point,

WITH TablesAndRowCounts
AS (
    SELECT SCHEMA_NAME(sc.schema_id) SchemaName
        ,sc.NAME + '.' + ta.NAME TableName
        ,SUM(pa.rows) RowCnt
    FROM sys.tables ta
    INNER JOIN sys.partitions pa ON pa.OBJECT_ID = ta.OBJECT_ID
    INNER JOIN sys.schemas sc ON ta.schema_id = sc.schema_id
    WHERE ta.is_ms_shipped = 0
        AND pa.index_id IN (1,0)
    GROUP BY sc.schema_id
        ,sc.NAME
        ,ta.NAME
    )
SELECT *
FROM TablesAndRowCounts
WHERE rowcnt = 0
and SchemaName = 'HR'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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