How to specify TABLE_SCHEMA name in sp_MSforeachdb to take database offline

Question

I need to search for certain databases with a particular prefix. Once these databases have been located, I need to check to see if a certain schema exists and if it does not exist - take the database offline. I have been trying various suggestions I found on Google but nothing works. I have one error that I cannot get past. The error is 'XYZ' is not a recognized option. If I separate out the code it runs fine but when I add sp_MSforeachdb, the error returns.

EXEC sp_msforeachdb 'IF ''?'' LIKE ''abc_%''
  BEGIN
      IF (NOT EXISTS  (SELECT *
               FROM INFORMATION_SCHEMA.TABLES 
               WHERE TABLE_SCHEMA = 'XYZ'          
                               BEGIN
                               ALTER DATABASE [abc_xxxxxxxxx] SET OFFLINE WITH
                               ROLLBACK IMMEDIATE             
                               END'  

This code works fine:

SELECT *
 FROM INFORMATION_SCHEMA.TABLES 
 WHERE TABLE_SCHEMA = 'XYZ'

Answer 1

Here's a method that doesn't rely on the undocumented, unsupported and buggy system procedure sp_msforeachdb, and also doesn't rely on similarly unreliable INFORMATION_SCHEMA views.

DECLARE @sql NVARCHAR(MAX);
SET @sql = N'';

SELECT @sql = @sql + N'IF NOT EXISTS (SELECT 1 FROM ' 
  + QUOTENAME(name) + '.sys.tables AS t
  INNER JOIN ' + QUOTENAME(name) + '.sys.schemas AS s
  ON t.schema_id = s.schema_id
  WHERE s.name = ''XYZ'')
  BEGIN
    EXEC sp_executesql N''ALTER DATABASE ' 
      + QUOTENAME(name) + ' SET OFFLINE;''
  END
' FROM sys.databases WHERE name LIKE 'abc_%';

PRINT @sql;
-- EXEC sp_executesql @sql;

Your actual error is because you have string delimiters inside a string. You can't do this:

SELECT ' WHERE TABLE_SCHEMA = 'XYZ' ... ';

Error:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ' ... '.

You need to do this:

SELECT ' WHERE TABLE_SCHEMA = ''XYZ'' ... ';

However this gets convoluted quite quickly when you're nesting commands inside a command you're sending to an undocumented, unsupported and buggy system procedure. I know I'm repeating myself here. I hope it's clear that you should not be using sp_msforeachdb if you want reliable results.

Answer 2

You had some minor errors

EXEC sp_msforeachdb '
IF ''?'' LIKE ''abc_%''
  BEGIN
      IF NOT EXISTS ( SELECT *
                      FROM INFORMATION_SCHEMA.TABLES 
                      WHERE TABLE_SCHEMA = ''XYZ'')
          ALTER DATABASE [abc_xxxxxxxxx] SET OFFLINE WITH ROLLBACK IMMEDIATE             
  END'

1. You have escaped the quotes around ''?'' and ''abc_%'' but forgot about 'XYZ'
2. The brackets were not matched - you have two opening brackets but no close brackets
3. The ALTER DATABASE statement is a single line and did not need BEGIN-END. Removing the BEGIN allows the END to match the other BEGIN, although you could remove the BEGIN-END altogether.

Answer 3

Try this one -

DECLARE 
      @schema_name SYSNAME
    , @database_name SYSNAME

SELECT 
      @schema_name = 'XYZ'
    --, @database_name = 'abc_%'

DECLARE @SQL NVARCHAR(MAX)

SELECT @SQL = (SELECT '
USE [' + d.name + '];
IF NOT EXISTS (
    SELECT 1 
    FROM sys.objects o
    JOIN sys.schemas s ON o.[schema_id] = s.[schema_id]
    WHERE o.type = ''U'' 
        AND s.name = ''' + @schema_name + '''
) EXEC sp_executesql N''ALTER DATABASE [' + d.name + '] SET OFFLINE;''
' 
FROM sys.databases d 
WHERE (@database_name IS NULL OR d.name LIKE @database_name)
    AND d.[state] = 0
    AND d.name NOT IN ('master', 'tempdb', 'model', 'msdb')
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')

EXEC sp_executesql @sql