SQL Server : query to loop through

I have a requirement to obtain all of the application databases and I have been able to obtain that through

select name from sys.database where database_id > 6

Now, I need to run the query below:

select 
   User_FirstName
   , User_Name
   , User_Id
   , User_ReadAccess
from 
    [name obtained from sys.databases].dbo.ADMN_User_Details

This is what I have written so far and I am getting an error at database_Name.dbo.ADMN.User_Details:

declare @database_name varchar(50)
declare @User_FirstName varchar (50)
declare @User_Name varchar (50)
declare @User_Access int
declare @User_Id varchar (50)

declare Maximizer cursor FOR

select name from sys.databases
where database_id > 6

open Maximizer
fetch next from Maximizer into @database_name

while (@@FETCH_STATUS=0)
begin

  select
      @User_FirstName, @User_Name, @User_Id, @User_Access 
  from 
      @database_Name.dbo.ADMN.User_Details
end

close Maximizer
deallocate Maximizer

Could someone please assist me here?

Thanks

You can use dynamic SQL for this so long as you're using this in a systems administration context and not out in the wild.

--@sql holds your dynamic sql, @db holds the current DB
declare @sql varchar(1000)
declare @db varchar(100)

--populate your list of databases through which you'll iterate.
select name, 0 as run 
into #dbs
from sys.databases where database_id > 6

--a while loop in sql...don't tell Jeff Moden
while exists (select 1 from #dbs where run = 0)
begin
    set @db = (select top 1 name from #dbs where run = 0 order by name)
    set @sql = '
    select ''' +  @db + '''
        ,   User_FirstName
        ,   User_Name
        ,   User_Id
        ,   User_ReadAccess
    from '+ @db +'.dbo.ADMN_User_Details'
    exec(@sql)
    update #dbs 
    set run = 1
    where name = @db    
end

A couple of things to keep in mind:

Your dynamic SQL variable needs to be long enough to hold the SQL content plus the database name.
I changed sys.database to sys.databases because that's the name of the table
If you need to aggregate the list of user names, IDs, and Read Accesses, I'd suggest creating another temp table. This might be a good idea because if you have a lot of databases, your result sets may get a little hard to manage.

You need to use dynamic sql to concatenate the variable name in your query, something like:

DECLARE @sql
declare @database_name varchar(50)
declare @User_FirstName varchar (50)
declare @User_Name varchar (50)
declare @User_Access int
declare @User_Id varchar (50)
declare @sql VARCHAR(MAX)

declare Maximizer cursor FOR

select name from sys.databases
where database_id > 6

open Maximizer
fetch next from Maximizer into @database_name

while (@@FETCH_STATUS=0)
begin

PRINT @database_Name
SET @sql = '
select '+@User_FirstName+', '+@User_Name+', '+@User_Id+', '+@User_Access+' from '+@database_Name+'.dbo.ADMN.User_Details
'
EXEC (@sql)
end

close Maximizer 
deallocate Maximizer

SQL Server : query to loop through

Related

Recent Posts