T-SQL script listing all empty tables on SQL Server

list empty tables.sql

USE master

DECLARE @dbName nvarchar(128);
DECLARE @sqlTableQuery nvarchar(max);
DECLARE dbsCursor CURSOR 
  LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR
SELECT
	name
FROM master.dbo.sysdatabases
WHERE DATALENGTH(sid) > 1  -- to avoid listing master, model, tmp databases 
ORDER BY 1

OPEN dbsCursor
FETCH NEXT FROM dbsCursor INTO @dbName
WHILE @@FETCH_STATUS = 0
BEGIN

SET @sqlTableQuery = N'PRINT('''')
USE ' + @dbName + '
PRINT(''' + @dbName + ''')
DECLARE tablesCursor CURSOR  
   FOR
SELECT
	s.name
	,t.name
FROM sys.objects AS t
JOIN sys.schemas AS s
	ON s.schema_id = t.schema_id
WHERE t.type = ''U'' ORDER BY 1, 2;
  
OPEN tablesCursor;
  
DECLARE @schemaname sysname;
  
DECLARE @tablename sysname;
  
FETCH NEXT FROM tablesCursor INTO @schemaname, @tablename;
  
DECLARE @tmpSqlQuery nvarchar(315);
WHILE (@@FETCH_STATUS <> -1)  
BEGIN;
DECLARE @from nvarchar(100)=@schemaname + ''.'' + @tablename
SET @tmpSqlQuery = N''IF NOT EXISTS (SELECT 1 FROM '' +@from+ '' )
BEGIN
PRINT('''''' + @from+'''''')
END''
-- PRINT (@tmpSqlQuery)
EXECUTE sys.sp_executesql @tmpSqlQuery
FETCH NEXT FROM tablesCursor INTO @schemaname, @tablename;
END;
  
CLOSE tablesCursor;
  
DEALLOCATE tablesCursor; '
-- PRINT (@sqlTableQuery)
EXECUTE sp_executesql @sqlTableQuery

FETCH NEXT FROM dbsCursor INTO @dbName
END
CLOSE dbsCursor
DEALLOCATE dbsCursor