Question :
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
Answer :
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'