Looping through list in a query

Hello,
I am trying to write a query that will execute the same command to each
database in a list. More specifically I use the following command to get a
list of all the user-defined databases on the server:
SELECT catalog_name from information_schema.Schemata
WHERE NOT (CATALOG_NAME in
('tempdb','master','msdb','model','Northwind','pub s'))
and now I want to perfrom that same action (ie dettach or check for orphan
users etc) on each DB that I get from this query. Is there a way to do this
in SQL?
thanks
christos
Christos Kritikos wrote:
> Hello,
> I am trying to write a query that will execute the same command to
> each database in a list. More specifically I use the following
> command to get a list of all the user-defined databases on the server:
> SELECT catalog_name from information_schema.Schemata
> WHERE NOT (CATALOG_NAME in
> ('tempdb','master','msdb','model','Northwind','pub s'))
> and now I want to perfrom that same action (ie dettach or check for
> orphan users etc) on each DB that I get from this query. Is there a
> way to do this in SQL?
> thanks
> christos
Checking user information can be done in sysprocesses. No need to
enumerate the databases. If you really need a way to run the same
command against each database, you can use xp_MSForEachDB or just use a
temp table and interate through the results.
David Gugick
Imceda Software
www.imceda.com