SQL Server : query to loop through

Question

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

It's fashionable to disparage cursor use, but they are the only efficient way to do some tasks. They are only less efficient if there is a more efficient way of doing it. — Hart CO
– Hart CO, Commented Oct 29, 2013 at 21:18

Paul Cauchon · Accepted Answer · 2013-10-30 21:47:13Z

9

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.

edited Oct 30, 2013 at 21:47

answered Oct 29, 2013 at 21:22

Paul Cauchon

5391 gold badge5 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user2933907 Over a year ago

It works great. Thank you very much. Really like all the suggestions. I am in need of one little modification. db_name() prints master as the name of the database. Is there a way to get the database name (@db) printed along with the rest of the user pertinent information?

Paul Cauchon Over a year ago

Good catch OP. db_name() gets the database in which the query is running. I changed the query to simply print whatever the current database is by parsing in the @db variable (with all of the necessary ticks).

user2933907 Over a year ago

Thanks very much for your help. This is now complete.

Hart CO · Accepted Answer · 2013-10-30 17:59:41Z

1

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

edited Oct 30, 2013 at 17:59

answered Oct 29, 2013 at 21:22

Hart CO

34.9k6 gold badges52 silver badges66 bronze badges

2 Comments

user2933907 Over a year ago

Thanks for the answer. I got an error - "conversion failed when converting the varchar value from to data type int". I changed the select statement and was able to correct the error. Is there a way, I can get the @database_name printed as well along with the rest of the user information? Thanks a bunch again.

Hart CO Over a year ago

If your variables aren't varchar then you'll have to cast them if concatenating with strings, but you've fixed that, feel free to edit the answer to show what you've done differently in the select statement. You can add a PRINT command at the onset, see updated answer.

Collectives™ on Stack Overflow

SQL Server : query to loop through

2 Answers 2

3 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related