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

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%'''

CodePudding user response：

Your original query says:

Return the database name when there is a table that does not match the pattern '%etl%' anywhere in the name

...which should return every database unless there is an empty database with no tables.

I think the query you want in any single database is flipped in a way (and also set to only find tables that start with etl):

SELECT DB_NAME() WHERE NOT EXISTS 
(SELECT 1 FROM sys.tables WHERE name LIKE N'etl%');

And to get it to run in each database (without having to do anything ugly to inject the database name everywhere, like ''?'' and [?]):

CREATE TABLE #dbs(db sysname);

DECLARE @exec nvarchar(1000),
        @sql  nvarchar(max);

SET @sql = N'SELECT DB_NAME() WHERE NOT EXISTS 
  (SELECT 1 FROM sys.tables WHERE name LIKE N''etl%'');';

DECLARE @dbname sysname, @C CURSOR;

SET @C = CURSOR FORWARD_ONLY STATIC READ_ONLY FOR
  SELECT name FROM sys.databases
  WHERE state = 0 AND database_id > 4;

OPEN @C;

FETCH NEXT FROM @C INTO @dbname;

WHILE @@FETCH_STATUS = 0
BEGIN
  SET @exec = QUOTENAME(@dbname)   N'.sys.sp_executesql';
  INSERT #dbs(db) EXEC @exec @sql;
  FETCH NEXT FROM @C INTO @dbname;
END

SELECT db FROM #dbs;

This will return the list of database names that do not contain a single table starting with etl (and results may vary if you have, say, ETL and the database or instance is case-sensitive).

You can do this without a cursor but I find the above a generally self-explanatory way to perform any "do x in every database" task. I talk about the @exec approach a lot more in "I want to do X to all the Ys in database Z".

DECLARE @sql nvarchar(max) = N'SELECT name = NULL WHERE 1 = 0';

SELECT @sql  = N'
  UNION ALL 
  SELECT [db] = N'   QUOTENAME(name, char(39)) 
    N' FROM '   QUOTENAME(name) 
    N'.sys.tables WHERE name LIKE N''etl%'''
FROM sys.databases
WHERE state = 0 AND database_id > 4;

EXEC sys.sp_executesql @sql;

More on your original attempt:

''?'' can break if your database name has an apostrophe
[?] is not a safe way to quote names - always use QUOTENAME()
sp_msforeachdb is undocumented, unsupported, and broken (see some links here)

CodePudding user response：

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;