I am trying to loop a query through multiple databases that I have listed in a table, I've tried to use a cursor and switch out the USE statement with the new database name but I keep getting an incorrect syntax error. Is there any way to do this? Below is my attempt.
Declare @storednamesDATABASE varchar(255)
Declare storednamesDBs_cursor Cursor
for
select Database_name from ##ALLTHEstorednames
Open storednamesDBs_cursor
Fetch next from storednamesDBs_cursor into @storednamesDATABASE
WHILE @@FETCH_STATUS = 0 BEGIN
Use @storednamesDATABASE
select distinct a.database_id, c.name as Database_Name, a.object_id, b.name as Table_Name,
dateadd(day, datediff(day, 0, getdate()),0) as Use_Date
from sys.dm_db_index_usage_stats a
join sys.all_objects b
on a.object_id = b.object_id
join sys.databases c
on a.database_id = c.database_id
where (a.last_user_seek between dateadd(hour, -24, CURRENT_TIMESTAMP) AND CURRENT_TIMESTAMP
or a.last_user_scan between dateadd(hour, -24, CURRENT_TIMESTAMP) AND CURRENT_TIMESTAMP
or a.last_user_lookup between dateadd(hour, -24, CURRENT_TIMESTAMP) AND CURRENT_TIMESTAMP)
and b.type = 'u'
End
Close storednamesDBs_cursor
First of all if you're on azure you can't use "use"
Your issue is that "use" doesn't take a string (nvarchar) it takes the object name.
To achieve this you could use dynamics SQL
Select @Sql = 'Use ' + @storednamesDATABASE + '; --Other SQL Here'
Exec sp_executesql @Sql
Though you're going to have to put the rest of your query in the Sql string because it won't change the connection you're currently in. It effectively creates a stored procedure and runs it.
To pass parameters to your query you can use something like
Exec sp_executesql @Sql, N'@Var1 int, @Var2 nvarchar(50)', @Var1 = @SomeVar, @Var2 = 'Test'