How to retrieve whether database has any tables?
The following from sys.tables will work. is_ms_shipped defines whether or not the object is a Microsoft table, however sys.tables should contain only user-defined tables

SELECT * FROM sys.tables WHERE is_ms_shipped = 0


To exclude sysdiagrams from the list, here is something I've borrowed from Aaron Bertrand on this forum

SELECT o.*
FROM sys.objects o
LEFT OUTER JOIN
sys.extended_properties ep
ON o.[object_id] = ep.major_id
AND ep.class_desc = 'OBJECT_OR_COLUMN'
AND ep.[name] = 'microsoft_database_tools_support'
WHERE
o.is_ms_shipped = 0
AND ep.major_id IS NULL 
AND type = 'U';


You can also check out this answer on Stack Overflow