A while back I was asked to identify schemas that didn’t have any objects. Since it was a useful query, I thought I’d share it.
SELECT s.name as schema_name
,COALESCE(o.type_desc, '--NO ITEMS--') as type_desc
,NULLIF(COUNT(DISTINCT o.name),0) as item_count
FROM sys.schemas s
LEFT OUTER JOIN sys.all_objects o ON s.schema_id = o.schema_id
WHERE s.name NOT IN ('INFORMATION_SCHEMA', 'sys')
GROUP BY s.name, o.type_desc
ORDER BY s.name, o.type_desc
Strate SQL 