Running a script/proc in multiple DB''s
-
Hi, my t-sql skills are fairly limited so I hope you can help with this. I'm trying to run the following, but as one proc so it runs the same query in each database on the server and gives me back one result set covering all DB's
select db_name() as [DB Name],
name as [User],
CASE hasdbaccess
WHEN '0' THEN 'NO'
WHEN '1' THEN 'YES'
END as [Has DB Access]
from sysusers
where name in ('guest','public')
I've tried a cursor, selecting each dbname from sysdatabases and then executing with sp_executesql but of course it execs from the DB its currently in.
Any pointers would be much appreciated.
Many Thanks
-
declare @db table (id int identity(0, 1), name sysname)
insert @db
(
name
)
select name
from master..sysdatabases
where dbid > 4 -- remove system databases
order by case when name = db_name() then 0 else 1 end
create table #output (name sysname, sysname, access bit)
declare @id int,
@name sysname,
@sql varchar(1000)
select @id = max(id)
from @db
while @id >= 0
begin
select @name = name,
@sql = 'USE ' + quotename(@name)
from @db
where id = @id
exec (@sql)
select @sql = 'insert #output (name, , access) select ''' + @name + ''', su.name, su.hasdbaccess from ' + quotename(@name) + '..sysusers su where su.name in (''guest'', ''public'')'
exec (@sql)
select @id = @id - 1
end
select * from #output
drop table #output
N 56°04'39.16"
E 12°55'05.25" -
Or...
create table #output (name sysname, sysname, access bit)
exec sp_MSforeachdb @command1 =
'insert #output select ''?'', name, hasdbaccess from ?.dbo.sysusers where name in (''guest'', ''public'')'
select * from #output where db_id(name) > 4
drop table #output
This uses the undocumented stored procedure sp_MSforeachdb, which you can read about here, should you wish:
http://www.databasejournal.com/features/mssql/article.php/3441031
Clearly this method is neat, but you should take into account the warning notes at the bottom of the above link.
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
Thats great, thanks very much to you both !
-
BTW- You can always tools like SQL Farm Combine to run the simple query against as many databases as you wish, across multiple servers as well.
Editor's Note: : Dr. Omri Bahat works for SQLFarms, a software vendor of SQL Server tools.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply