Execute the query in one server with different database

Question

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

Answer 1

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

Answer 2

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.

Answer 3

I ran the above script but gets the following error:

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

Answer 4

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

Answer 5

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.