Is there a way to use `exec` in a where clause?

Question

I am trying to query an instance of SQL Server to give me a list of databases that contain a table of a specific name. This is what I have so far...

select name
from master..sysdatabases
where (exec('use ' + name + '; select 1 from information_schema.tables 
  where table_name = ''TheTableName'';')) = 1;

But I get the following error messages

Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'exec'.
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near 'name'.

What is the correct syntax to use call exec() in a where clause? Or is there another way to do what I'm trying to do?

Answer 1

No, you cannot use exec in a where clause. How about some dynamic SQL:

DECLARE @sql NVARCHAR(MAX);

SET @sql = N'SELECT name = NULL WHERE 1 = 0';

SELECT @sql = @sql + N'
  UNION ALL SELECT name = ''' + name + ''' 
  WHERE EXISTS (SELECT 1 FROM ' + QUOTENAME(name)
  + '.sys.tables WHERE name = ''TheTableName'')'
  FROM sys.databases;

EXEC sp_executesql @sql;

Answer 2

Powershell was built for this:

$server = new-object Microsoft.SqlServer.Management.Smo.Server ".";
foreach ($db in $server.Databases) {
   $t = $db.Tables | where {$_.Schema -eq 'YourSchema' -and $_.Name -eq 'TableName'};
   if ($t -ne $null) {
      $db.Name;
   }
}