querying several databases using a single cursor

[penzk001]

I would like to run a procuedure to determine the amount to users that have access to some to the production databases.

I came up with the following procedure, however I am having troubles when add the query for each user database.

DECLARE @db_name varchar(50), @db_users integer, @tab_name varchar(300);
declare db_cursor CURSOR for
select name from sysdatabases where name not in ('master','model','msdb','tempdb');
open db_cursor;
FETCH NEXT FROM db_cursor INTO @db_name;
WHILE @@FETCH_STATUS = 0
BEGIN
set @tab_name = @db_name;
print @tab_name;
FETCH NEXT FROM db_cursor INTO @db_name;
-- select name from @tab_name where name not in ('db_accessadmin','db_backupoperator','db_datareader','db_datawriter','db_ddladmin','db_denydatareader','db_denydatawriter','db_owner','db_securityadmin','dbo','guest','public');
end;
CLOSE db_cursor;
DEALLOCATE db_cursor;


What am I doing wrong ?

 

[royjimenez]

hi!
u have to make a dynamic cursor.


I am sending u a old example i have:
try to get all that u need:

create PROC ba_conc_saldos_diarios
@tableowner sysname, -- this is the publisher owner, but we assume its always dbo at subscriber.
@tablename sysname, -- this is the publisher tablename, but we assume its identical at subscriber.
@rowguid uniqueidentifier,
@subscriber sysname,
@subscriber_db sysname,
@log_conflict int OUTPUT, -- output param for if to log conflict for later resolution.
@conflict_message nvarchar(512) OUTPUT -- output param for message to be given about resolution if conflict is loggged.

AS

declare @sqlstr varchar(7000),
@pub_qualified_name varchar(392),
@sub_qualified_name varchar(392),
@empresa varchar(5),
@cuenta decimal(8),
@fcierre datetime,
@finicio datetime,
@ffinal datetime
--sfcierre varchar(20)

declare @mon_salini_sub decimal(15,2),
@mon_salini decimal(15,2),
@mon_salfin decimal(15,2),
@mon_salfin_sub decimal(15,2)

SELECT @pub_qualified_name=QUOTENAME(@subscriber_db)+'.'+QUOTENAME(@tableowner)+'.'+QUOTENAME(@tablename)
SELECT @sub_qualified_name=QUOTENAME(@subscriber_db)+'.'+QUOTENAME(@tableowner)+'.'+QUOTENAME(@tablename)

-- Obtener los datos de Publicador
select @sqlstr = "SELECT SALDO_INI, SALDO_ACT FROM " + @pub_qualified_name + " where ROWGUIDCOL = '" + convert(varchar(36),@rowguid) + "'"
select @sqlstr = "DECLARE lcRepl CURSOR GLOBAL FAST_FORWARD FOR " + @sqlstr
execute ( @sqlstr )
if @@error <> 0 goto FALLO
open lcRepl
fetch next from lcRepl into @mon_salini, @mon_salfin
close lcRepl
deallocate lcRepl

-- Recuperar los datos del Subscriptor
select @sqlstr = 'SELECT * FROM ' + @sub_qualified_name + &quot; where ROWGUIDCOL = '&quot; + convert(varchar(36),@rowguid) + &quot;'&quot;
select @sqlstr = &quot;select COD_EMPRESA, ID_CUENTA, FEC_CIERRE from openquery(&quot; + QUOTENAME(@subscriber) + ',&quot;' + @sqlstr + '&quot'
select @sqlstr = 'DECLARE lcReplSaldos CURSOR GLOBAL FAST_FORWARD FOR ' + @sqlstr
execute ( @sqlstr )
if @@error <> 0 goto FALLO
open lcReplSaldos
fetch next from lcReplSaldos into @empresa, @cuenta, @fcierre
close lcReplSaldos
deallocate lcReplSaldos

select @finicio = convert(datetime, convert(char(4), datepart(year, @fcierre)) + &quot;-&quot; + convert(varchar(2), datepart(month, @fcierre)) + &quot;-&quot; + convert(varchar(2),datepart(day, @fcierre)))
select @ffinal = dateadd(day, 1, @finicio)

-- Obtener el Detalle de Movimientos
select @sqlstr = QUOTENAME(@subscriber_db)+'.'+QUOTENAME(@tableowner)+'.'+QUOTENAME(@tablename + '_DESGLOSE')
select @sqlstr = &quot;select * from &quot; + @sqlstr + &quot; where COD_EMPRESA = '&quot; + @empresa + &quot;' and ID_CUENTA = &quot; + convert(varchar(10), @cuenta) + &quot; and FEC_CIERRE >= '&quot; + convert(varchar(30), @finicio) + &quot;' and FEC_CIERRE < '&quot; + convert(varchar(30), @ffinal) + &quot;'&quot;
select @sqlstr = 'select COD_EMPRESA, ID_CUENTA, FEC_CIERRE, sum(SALDO_INI), sum(SALDO_ACT) from openquery(' + QUOTENAME(@subscriber) + ',&quot;' + @sqlstr + '&quot group by COD_EMPRESA, ID_CUENTA, FEC_CIERRE'
select @sqlstr = &quot;DECLARE lcReplSaldosDet CURSOR GLOBAL FAST_FORWARD FOR &quot; + @sqlstr
execute ( @sqlstr )
if @@error <> 0 goto FALLO
open lcReplSaldosDet
fetch next from lcReplSaldosDet into @empresa, @cuenta, @fcierre, @mon_salini_sub, @mon_salfin_sub
close lcReplSaldosDet
deallocate lcReplSaldosDet

-- Calcular el Monto de Desfase
select @mon_salini = isnull(@mon_salini,0) + isnull(@mon_salini_sub,0)
select @mon_salfin = isnull(@mon_salfin,0) + isnull(@mon_salfin_sub,0)

select @sqlstr = QUOTENAME(@subscriber_db)+'.'+QUOTENAME(@tableowner)+'.'+QUOTENAME(@tablename + '_DESGLOSE')
select @sqlstr = &quot;delete from [&quot; + @subscriber + &quot;].&quot; + @sqlstr + &quot; where COD_EMPRESA = '&quot; + @empresa + &quot;' and ID_CUENTA = &quot; + convert(varchar(10), @cuenta ) + &quot; and FEC_CIERRE >= '&quot; + convert(varchar(30), @finicio) + &quot;' and FEC_CIERRE < '&quot; + convert(varchar(30), @ffinal) + &quot;'&quot;
execute( @sqlstr )
if @@error <> 0 goto FALLO

-- Devolver el Cursor
SELECT COD_EMPRESA, ID_CUENTA, FEC_CIERRE, @mon_salini, @mon_salfin, @rowguid
FROM BA.BA_SALDOS_DIARIOS
WHERE rowguid=@rowguid
if @@error <> 0 goto FALLO

RETURN 1

FALLO:
raiserror 40001 &quot;Error al realizar la replicacion&quot;
return -1

 

[Jamfool]

You can either use a dynamic cursor as follows

DECLARE @db_name varchar(50), @db_users integer, @tab_name varchar(300);
declare db_cursor CURSOR for
select name from sysdatabases where name not in ('master','model','msdb','tempdb');
open db_cursor;
FETCH NEXT FROM db_cursor INTO @db_name;
WHILE @@FETCH_STATUS = 0
BEGIN
set @tab_name = @db_name;
print @tab_name;
FETCH NEXT FROM db_cursor INTO @db_name;
color]
declare @sql as varchar (1000)
set @sql = 'select name from '+@db_name+' where name not in ('
+'''db_accessadmin'''+','+'''db_backupoperator'''+')'
print @sql
exec (@sql)

end;
CLOSE db_cursor;
DEALLOCATE db_cursor;


or insert your code into something like this


EXEC sp_MSForEachDB 'IF ''?'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'')
BEGIN PRINT ''Database Query Running On ?''
use ?
select * from user_info
END '

 

[penzk001]

Thanks for your help, I managed to sort it out with your help. Again thanks