CODEWITHSUNDEEP
sql-server
Nick Strupat
Nick Strupat

Reputation: 5063

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

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?

Upvotes: 6

Views: 7797

Answers (5)

Eric Frick
Eric Frick

Reputation: 857

You can also store the results from the Stored Procedure in a temporary table, and then add it to the WHERE clause

Upvotes: 0

Ben Thul
Ben Thul

Reputation: 32697

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;
   }
}

Upvotes: 2

Tony Hopkinson
Tony Hopkinson

Reputation: 20320

You have to use temporary table to get information back from exec.

Try this

Create Table #TableLocations(DatabaseName varchar(255) not null)
Declare @databaseName VarChar(255)
Declare @QueryString VarChar(255)
Declare DatabaseNameCursor Cursor For Select [Name] From sys.databases
Declare @TableName VarChar(255)
Select @TableName = 'Put your table name here'
Open DatabaseNameCursor
Fetch Next From DatabaseNameCursor Into @databaseName
While @@Fetch_Status = 0
Begin
  Select @QueryString = 'Use ' + @databaseName + ' Insert into #TableLocations Select ''' + @databaseName + ''' From information_schema.tables Where Table_Name = ''' + @TableName + ''''
  Exec(@QueryString)
  Fetch Next From DatabaseNameCursor Into @databaseName 
End
Close DatabaseNameCursor
DeAllocate DataBaseNameCursor
Select * from #TableLocations
Drop Table #TableLocations

Upvotes: 0

schellack
schellack

Reputation: 10274

This SQL statement will give you all of the database names that contain the table you are looking for:

EXEC sp_MSForEachDB 'USE [?]; select ''?'' from information_schema.tables where table_name = ''TheTableName''' ;

Upvotes: 1

anon
anon

Reputation:

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;

Upvotes: 4

Related Questions