|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Select all databases and run a select queries against eachHello,
I was able to select all databases from master..sysdatabases, but I am not sure how to run queries against each row so I have something like: "database" "query result" "database2" "query result" .... Any ideas? PS: Each query should run against a different database If you have a low number of databases (e.g. < 255) you can do this:
EXEC master.dbo.sp_msForEachDB 'EXEC ?.dbo.ProcedureName;' sp_msForEachDB is undocumented and unsupported, so use at your own risk. Also see http://omnibuzz-sql.blogspot.com/2006/07/useful-undocumented-procedures-in-sql.html I posted a reply that included my own cursor-style version of sp_msForEachDB (well, admittedly, it's for SQL Server 2005). Show quote "Skeptical" <gun***@gmail.com> wrote in message news:1155220260.831058.32280@h48g2000cwc.googlegroups.com... > Hello, > > I was able to select all databases from master..sysdatabases, but I am > not sure how to run queries against each row so I have something like: > > "database" "query result" > > "database2" "query result" > > ... > > Any ideas? > > PS: Each query should run against a different database > Are the queries the same? Can you provide an example of the query?
Show quote "Skeptical" wrote: > Hello, > > I was able to select all databases from master..sysdatabases, but I am > not sure how to run queries against each row so I have something like: > > "database" "query result" > > "database2" "query result" > > .... > > Any ideas? > > PS: Each query should run against a different database > > well yes they are the same, basically I need a count of the number of
items from the same table in every database. select count (*) from products where type='A' I tried using a cursor but that generated lots of seperate tables. I would like to get all the results in one table if I could. Steve Mann wrote: Show quote > Are the queries the same? Can you provide an example of the query? > > -- > RDA Corp > Business Intelligence Evangelist Leader > www.rdacorp.com > > > "Skeptical" wrote: > > > Hello, > > > > I was able to select all databases from master..sysdatabases, but I am > > not sure how to run queries against each row so I have something like: > > > > "database" "query result" > > > > "database2" "query result" > > > > .... > > > > Any ideas? > > > > PS: Each query should run against a different database > > > > As Aaron mentioned, if your database count is less than 255, you can use the
sp_msForEachDB undocumented procedure. And you may be able to use a single #Temp table created beforehand and passed into the line of code to execute. -- Show quoteArnie Rowland, Ph.D. Westwood Consulting, Inc Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous "Skeptical" <gun***@gmail.com> wrote in message news:1155222764.673763.277920@h48g2000cwc.googlegroups.com... > well yes they are the same, basically I need a count of the number of > items from the same table in every database. > > select count (*) from products where type='A' > > I tried using a cursor but that generated lots of seperate tables. I > would like to get all the results in one table if I could. > > Steve Mann wrote: >> Are the queries the same? Can you provide an example of the query? >> >> -- >> RDA Corp >> Business Intelligence Evangelist Leader >> www.rdacorp.com >> >> >> "Skeptical" wrote: >> >> > Hello, >> > >> > I was able to select all databases from master..sysdatabases, but I am >> > not sure how to run queries against each row so I have something like: >> > >> > "database" "query result" >> > >> > "database2" "query result" >> > >> > .... >> > >> > Any ideas? >> > >> > PS: Each query should run against a different database >> > >> > > Thanks but that procedure will not give me one table but x number of
tables x being the number of databases in the system. Arnie Rowland wrote: Show quote > As Aaron mentioned, if your database count is less than 255, you can use the > sp_msForEachDB undocumented procedure. And you may be able to use a single > #Temp table created beforehand and passed into the line of code to execute. > > -- > Arnie Rowland, Ph.D. > Westwood Consulting, Inc > > Most good judgment comes from experience. > Most experience comes from bad judgment. > - Anonymous > > > "Skeptical" <gun***@gmail.com> wrote in message > news:1155222764.673763.277920@h48g2000cwc.googlegroups.com... > > well yes they are the same, basically I need a count of the number of > > items from the same table in every database. > > > > select count (*) from products where type='A' > > > > I tried using a cursor but that generated lots of seperate tables. I > > would like to get all the results in one table if I could. > > > > Steve Mann wrote: > >> Are the queries the same? Can you provide an example of the query? > >> > >> -- > >> RDA Corp > >> Business Intelligence Evangelist Leader > >> www.rdacorp.com > >> > >> > >> "Skeptical" wrote: > >> > >> > Hello, > >> > > >> > I was able to select all databases from master..sysdatabases, but I am > >> > not sure how to run queries against each row so I have something like: > >> > > >> > "database" "query result" > >> > > >> > "database2" "query result" > >> > > >> > .... > >> > > >> > Any ideas? > >> > > >> > PS: Each query should run against a different database > >> > > >> > > > That's not correct. You can easily capture the results in one table. But it
sounds like you have your mind made otherwise, so, ... -- Show quoteArnie Rowland, Ph.D. Westwood Consulting, Inc Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous "Skeptical" <gun***@gmail.com> wrote in message news:1155848245.818731.177140@m73g2000cwd.googlegroups.com... > Thanks but that procedure will not give me one table but x number of > tables x being the number of databases in the system. > > > Arnie Rowland wrote: >> As Aaron mentioned, if your database count is less than 255, you can use >> the >> sp_msForEachDB undocumented procedure. And you may be able to use a >> single >> #Temp table created beforehand and passed into the line of code to >> execute. >> >> -- >> Arnie Rowland, Ph.D. >> Westwood Consulting, Inc >> >> Most good judgment comes from experience. >> Most experience comes from bad judgment. >> - Anonymous >> >> >> "Skeptical" <gun***@gmail.com> wrote in message >> news:1155222764.673763.277920@h48g2000cwc.googlegroups.com... >> > well yes they are the same, basically I need a count of the number of >> > items from the same table in every database. >> > >> > select count (*) from products where type='A' >> > >> > I tried using a cursor but that generated lots of seperate tables. I >> > would like to get all the results in one table if I could. >> > >> > Steve Mann wrote: >> >> Are the queries the same? Can you provide an example of the query? >> >> >> >> -- >> >> RDA Corp >> >> Business Intelligence Evangelist Leader >> >> www.rdacorp.com >> >> >> >> >> >> "Skeptical" wrote: >> >> >> >> > Hello, >> >> > >> >> > I was able to select all databases from master..sysdatabases, but I >> >> > am >> >> > not sure how to run queries against each row so I have something >> >> > like: >> >> > >> >> > "database" "query result" >> >> > >> >> > "database2" "query result" >> >> > >> >> > .... >> >> > >> >> > Any ideas? >> >> > >> >> > PS: Each query should run against a different database >> >> > >> >> > >> > > I am sorry, I misunderstood. Thank you all it works like a charm now!
Arnie Rowland wrote: Show quote > That's not correct. You can easily capture the results in one table. But it > sounds like you have your mind made otherwise, so, ... > > -- > Arnie Rowland, Ph.D. > Westwood Consulting, Inc > > Most good judgment comes from experience. > Most experience comes from bad judgment. > - Anonymous > > > "Skeptical" <gun***@gmail.com> wrote in message > news:1155848245.818731.177140@m73g2000cwd.googlegroups.com... > > Thanks but that procedure will not give me one table but x number of > > tables x being the number of databases in the system. > > > > > > Arnie Rowland wrote: > >> As Aaron mentioned, if your database count is less than 255, you can use > >> the > >> sp_msForEachDB undocumented procedure. And you may be able to use a > >> single > >> #Temp table created beforehand and passed into the line of code to > >> execute. > >> > >> -- > >> Arnie Rowland, Ph.D. > >> Westwood Consulting, Inc > >> > >> Most good judgment comes from experience. > >> Most experience comes from bad judgment. > >> - Anonymous > >> > >> > >> "Skeptical" <gun***@gmail.com> wrote in message > >> news:1155222764.673763.277920@h48g2000cwc.googlegroups.com... > >> > well yes they are the same, basically I need a count of the number of > >> > items from the same table in every database. > >> > > >> > select count (*) from products where type='A' > >> > > >> > I tried using a cursor but that generated lots of seperate tables. I > >> > would like to get all the results in one table if I could. > >> > > >> > Steve Mann wrote: > >> >> Are the queries the same? Can you provide an example of the query? > >> >> > >> >> -- > >> >> RDA Corp > >> >> Business Intelligence Evangelist Leader > >> >> www.rdacorp.com > >> >> > >> >> > >> >> "Skeptical" wrote: > >> >> > >> >> > Hello, > >> >> > > >> >> > I was able to select all databases from master..sysdatabases, but I > >> >> > am > >> >> > not sure how to run queries against each row so I have something > >> >> > like: > >> >> > > >> >> > "database" "query result" > >> >> > > >> >> > "database2" "query result" > >> >> > > >> >> > .... > >> >> > > >> >> > Any ideas? > >> >> > > >> >> > PS: Each query should run against a different database > >> >> > > >> >> > > >> > > > But as Arnie said, you can use a temporary table to load in all the result
from each database. For example, the following loads into one table the number of rows in the sysobjects table in each database. Create Table #TestTable (DatabaseName sysname, NbrRows int) exec sp_MSForEachDB 'Insert Into #TestTable (DatabaseName, NbrRows) Select ''?'', Count(*) From [?]..sysobjects' Select * From #TestTable Order By DatabaseName go Drop Table #TestTable Show quote "Skeptical" <gun***@gmail.com> wrote in message news:1155848245.818731.177140@m73g2000cwd.googlegroups.com... > Thanks but that procedure will not give me one table but x number of > tables x being the number of databases in the system. > > > Arnie Rowland wrote: >> As Aaron mentioned, if your database count is less than 255, you can use >> the >> sp_msForEachDB undocumented procedure. And you may be able to use a >> single >> #Temp table created beforehand and passed into the line of code to >> execute. >> >> -- >> Arnie Rowland, Ph.D. >> Westwood Consulting, Inc >> >> Most good judgment comes from experience. >> Most experience comes from bad judgment. >> - Anonymous >> >> >> "Skeptical" <gun***@gmail.com> wrote in message >> news:1155222764.673763.277920@h48g2000cwc.googlegroups.com... >> > well yes they are the same, basically I need a count of the number of >> > items from the same table in every database. >> > >> > select count (*) from products where type='A' >> > >> > I tried using a cursor but that generated lots of seperate tables. I >> > would like to get all the results in one table if I could. >> > >> > Steve Mann wrote: >> >> Are the queries the same? Can you provide an example of the query? >> >> >> >> -- >> >> RDA Corp >> >> Business Intelligence Evangelist Leader >> >> www.rdacorp.com >> >> >> >> >> >> "Skeptical" wrote: >> >> >> >> > Hello, >> >> > >> >> > I was able to select all databases from master..sysdatabases, but I >> >> > am >> >> > not sure how to run queries against each row so I have something >> >> > like: >> >> > >> >> > "database" "query result" >> >> > >> >> > "database2" "query result" >> >> > >> >> > .... >> >> > >> >> > Any ideas? >> >> > >> >> > PS: Each query should run against a different database >> >> > >> >> > >> > >  |
|||||||||||||||||||||||
Other interesting topics