Running a SQL select statement on every database on my Azure Server

Question

I want to run a select statement on every database hosted on my Azure instance. I have tried the below approach but I'm getting this error:

Msg 40508, Level 16, State 1, Line 21
USE statement is not supported to switch between databases. Use a new connection to connect to a different database.

Code:

DECLARE @DB_Name varchar(100) 
DECLARE @Command nvarchar(200) 

DECLARE database_cursor CURSOR FOR 
    SELECT name 
    FROM MASTER.sys.sysdatabases 

OPEN database_cursor 

FETCH NEXT FROM database_cursor INTO @DB_Name 

WHILE @@FETCH_STATUS = 0 
BEGIN 
     SELECT @Command = 'use LogDB1; Select * from Log'
     EXEC sp_executesql @Command 

     FETCH NEXT FROM database_cursor INTO @DB_Name 
END 

CLOSE database_cursor 
DEALLOCATE database_cursor

As the error tells you, that sort of syntax is *not* supported on Azure SQL Databases. The Application needs to connect to the database you need/want to use. If you need to query multiple databases in a single session, then Azure SQL Database is not the right tool for you. — Thom A, May 12 '21 at 13:43

score 1 · Answer 1 · answered May 12 '21 at 14:09

1

You are nearly there.

As @Larnu said querying all databases on your Azure instance is not supported.

My suggestion is: don't use SSMS. Use PowerShell to automate a new connection to every database.

You are not the only one having faced this problem.

answered May 12 '21 at 14:09

Francesco Mantovani

10,216
13
73
113

score 0 · Answer 2 · answered May 12 '21 at 23:27

You can query that log table on all databases by using PowerShell as mentioned by Francesco but you can also create an external table for each database where that table resides. Once you create an external table, you can then perform cross database queries on Azure SQL Database, and this is called elastic queries. Please read here to find more details.

Running a SQL select statement on every database on my Azure Server

2 Answers2