How do you check a server for databases missing a specific table?

user_16018913 0
Viewed 59

I am attempting to locate all databases on a server that are missing a specific table. I've tried the query below but it is returning databases that actually do have the table. I know there is a problem with the query but I do not know how to fix it. I'm very beginner with sql knowledge.

So trying to find all databases on the server that do not have tables that begin with a name of etl.

EXEC sys.sp_msforeachdb 'SELECT ''?'' DatabaseName, Name FROM [?].sys.Tables WHERE Name NOT LIKE ''%etl%'''

sql-server sql
1 Answers

A cursor is not necessary here at all. You can build a big UNION ALL query to check each database for these tables.

DECLARE @sql nvarchar(max);

SELECT @sql = STRING_AGG(CAST('
SELECT ' + QUOTENAME(d.name, '''') + '
WHERE NOT EXISTS (SELECT 1
    FROM ' + QUOTENAME(d.name) + '.sys.tables t
    WHERE t.name LIKE N''etl%'')
' AS nvarchar(max)), ' UNION ALL ')

FROM sys.databases d;

EXEC sp_executesql @sql;
user_148689970
Related