How to find list of tables having no records in SQL server

Alteration to add Schema names:

SELECT 
    sch.name,
    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

Try this:

SELECT 
    t.NAME AS TableName,
    p.rows AS RowCounts
FROM 
    sys.tables t
INNER JOIN 
    sys.partitions p ON t.object_id = p.OBJECT_ID 
WHERE 
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND p.rows = 0
GROUP BY 
    t.Name, p.Rows
ORDER BY 
    t.Name

The query goes to the sys.tables and other catalog views to find the tables, their indexes and partitions, to find those tables that have a row count of 0.