Please help with sp_MSforEachDB 1

[BikeBoy20032004]

Hi,

Can someone please tell me how to capture the data from sp_MSforEachDB into a variable?

I tried #temp, no luck.
I tried:

declare @dbname table (Col1 varchar (50))

EXEC sp_MSForEachDb 'IF EXISTS (SELECT 1 FROM ?.dbo.sysusers WHERE name = ''whoever'' AND isntname = ''1'')
''? '''

select * from @dbname

no joy.

Finally:
declare @myTable table (
col1 varchar (50) null
)


declare @command1 nvarchar(2000)
set @command1 = 'Insert into @dbname ''?'''
exec @myTable = sp_MSforeachdb @command1 = @command1

still no luck!

 

[mrdenny]

Capturing data back from sp_MSForEachDB is a major pain. When I need to return data back I ususlly to break down, and write my own cursor for the sysdatabases table, and then use dynamic SQL to get the data that I need, and pump it into a tempory table, so that I can query it at the end of the procedure.

It appears from your code that you are simply trying to get a list of all the databases. This will do that for you.

select name
from master.dbo.sysdatabases

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[BikeBoy20032004]

actually, I'm trying to capture the roles that a specific user belongs to in all of the databases.

I am not very good with cursors, that's why I wanted to use sp_MSForEachDB

 

[mrdenny]

ok, it took some serious looking around on my part, but here is the code needed.

I'm totally making a FAQ for this.

Simply change the set @UserName = 'username' to the username of the person to check on. It will return the databases they are in, and all the roles that they have.

declare @RoleName varchar(50)
declare @UserName varchar(50)
declare @CMD varchar(1000)

set @UserName = 'username'

create Table #UserRoles
(DatabaseName varchar(50), 
Role varchar(50))

create table #RoleMember
(DBRole varchar(100),
MemberName varchar(100),
MemberSid varbinary(2048))


set @CMD = 'use ?

truncate table #RoleMember

insert into #RoleMember
exec sp_helprolemember 

insert into #UserRoles
(DatabaseName, Role)
select db_name(), dbRole
from #RoleMember
where MemberName = ''' + @UserName + ''''

exec sp_MSForEachDB @CMD

select * from #UserRoles

drop table #UserRoles
drop table #RoleMember

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[mrdenny]

The FAQ is up at faq962-5411

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[BikeBoy20032004]

Thanks man, you de man!

 

[mrdenny]

no problem.

Denny

--Anything is possible. All it takes is a little research. (Me)