Execute the query in one server with different database

Mike 341 Reputation points
2021-02-18T04:22:24.39+00:00

Hi, I want to execute the Select query in all databases of Server_A. Will I have to write 'USE' command 4 times with databases name of Server_A. Currently I have 4 database so I have written 4 times but it could be more. Is there a way to run query in all databases of Server_A? OR will I have to write 'Select query' 10 times if I have 10 databases in Server_A?

:CONNECT Server_A
Use Grt_B
Select * from Fty where name like '%Test%'
GO

Use Trt_B
Select * from Fty where name like '%Test%'
GO

Use Trt_C
Select * from Fty where name like '%Test%'
GO

Use Frt_A
Select * from Fty where name like '%Test%'
GO

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,586 questions
0 comments No comments
{count} votes

Accepted answer
  1. Dan Guzman 9,221 Reputation points
    2021-02-18T13:06:56.6+00:00

    Instead of the undocumented and unsupported sp_MSforeachdb procedure, consider building a batch with USE statements for each database and conditionally execute the query only when the table exists in the database.

    :CONNECT Server_A
    DECLARE @SQL nvarchar(MAX) = (
    SELECT STRING_AGG(N'USE ' + QUOTENAME(name) + CAST(N';
    IF OBJECT_ID(N''dbo.Fty'') IS NOT NULL
    BEGIN
     SELECT * FROM Fty WHERE name LIKE ''%Test%'';
    END
    ' AS nvarchar(MAX)),';')
    FROM sys.databases
    );
    EXEC sp_executesql @SQL;
    GO
    

    Pre-SQL Server 2017 where STRING_AGG is unavailable, use FOR XML for aggregate string concatenation:

    DECLARE @SQL nvarchar(MAX) = STUFF((
    SELECT N';
    USE ' + QUOTENAME(name) + CAST(N';
    IF OBJECT_ID(N''dbo.Fty'') IS NOT NULL
    BEGIN
        SELECT * FROM Fty WHERE name LIKE ''%Test%'';
    END' AS nvarchar(MAX))
    FROM sys.databases
    FOR XML PATH(''), TYPE
    ).value('text()[1]', 'nvarchar(MAX)'),1,1,'') + ';';
    
    EXEC sp_executesql @SQL;
    GO
    
    1 person found this answer helpful.

4 additional answers

Sort by: Most helpful
  1. EchoLiu-MSFT 14,576 Reputation points
    2021-02-18T06:54:34.383+00:00

    Hi @Mike ,

    Please refer to:

        DECLARE @command varchar(1000)   
        SELECT @command = 'USE ? SELECT * FROM Fty where name like ''%Test%'' '   
        EXEC sp_MSforeachdb @command   
    

    Regards
    Echo


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

  2. Mike 341 Reputation points
    2021-02-18T12:44:42.037+00:00

    I ran the above script but gets the following error:

    Msg 208, Level 16, State 1, Line 1
    Invalid object name Fty

    0 comments No comments

  3. Mike 341 Reputation points
    2021-02-18T12:48:19.08+00:00

    It works with the following. Should I use first database of the server in the query?
    DECLARE @commandcommand varchar(1000)
    SELECT @commandcommand = 'USE Grt_B Select * from Fty where name like ''%Test%'' '
    EXEC sp_MSforeachdb @commandcommand

    0 comments No comments

  4. Mike 341 Reputation points
    2021-02-18T15:11:02.607+00:00

    The Query is giving me error. I have below SQL Server version than 2016. I have to run SQL query in all database of Server_A.

    'STRING_AGG' is not a recognized built-in function name.