 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / DB Engine / SQL Server / March 2008querying different databases
Hi, I need to query different databases. Below is an example (the database name is returned in 'select dbname'): select dbname, (select count(*) from [dbname].dbo.members) as 'mycount' from databases Can someone help with the syntax of the middle line in the query please? Three-part naming is what you are supposed to use and you are doing that. Could you post your DDL and tell us exactly what the issue is?  SignatureTom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS SQL Server MVP Toronto, ON Canada https://mvp.support.microsoft.com/profile/Tom.Moreau Hi, I need to query different databases. Below is an example (the database name is returned in 'select dbname'): select dbname, (select count(*) from [dbname].dbo.members) as 'mycount' from databases Can someone help with the syntax of the middle line in the query please? It gives me 'Invalid object name 'dbName.dbo.member'.' So it is not using the record value as the database. Using SQL2K. I don't want to loop through all databases on the server, just the ones returned in a query on the database called 'databases'. >Three-part naming is what you are supposed to use and you are doing that. >Could you post your DDL and tell us exactly what the issue is? Hi, I need to query different databases. Below is an example (the database name is returned in 'select dbname'): select dbname, (select count(*) from [dbname].dbo.members) as 'mycount' from databases Can someone help with the syntax of the middle line in the query please? So is there a database named dbname and is there a table in it named member? SignatureTom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS SQL Server MVP Toronto, ON Canada https://mvp.support.microsoft.com/profile/Tom.Moreau It gives me 'Invalid object name 'dbName.dbo.member'.' So it is not using the record value as the database. Using SQL2K. I don't want to loop through all databases on the server, just the ones returned in a query on the database called 'databases'. On Fri, 14 Mar 2008 13:20:08 -0400, "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote: >Three-part naming is what you are supposed to use and you are doing that. >Could you post your DDL and tell us exactly what the issue is? Hi, I need to query different databases. Below is an example (the database name is returned in 'select dbname'): select dbname, (select count(*) from [dbname].dbo.members) as 'mycount' from databases Can someone help with the syntax of the middle line in the query please? No, what I'm asking for is how to evaluate the dbName value and use it in the count part of the query. i.e. i have 3 databases, and i run the query while connected to dbMain: dbMain table: databases data: indx dbName 1 dbOne 2 dbTwo ---------------- dbOne: table: members data: index firstname 1 fred 2 barney ---------------- dbTwo: table: members data: index firstname 1 wilma 2 betty 3 bam-bam 4 pebbles ....and i need the results to be: dbOne 2 dbTwo 4 ..so dbMain is used to store the database names i need to get the member counts from. ..if i use the query: select dbName, (select count(*) from dbOne.dbo.members) as 'mycount' from databases i get: dbOne 2 dbTwo 2 (instead of 4) ..because of the hard-coded database name. >--------------------------------------------------- On Fri, 14 Mar 2008 14:49:08 -0400, "Tom Moreau" <So is there a database named dbname and is there a table in it named member? SignatureTom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS SQL Server MVP Toronto, ON Canada https://mvp.support.microsoft.com/profile/Tom.Moreau <a@b.com> wrote in message news:p4hlt3pauket90l511tmlnn6rmebq19kij@4ax.com... It gives me 'Invalid object name 'dbName.dbo.member'.' So it is not using the record value as the database. Using SQL2K. I don't want to loop through all databases on the server, just the ones returned in a query on the database called 'databases'. >Three-part naming is what you are supposed to use and you are doing that. >Could you post your DDL and tell us exactly what the issue is? Hi, I need to query different databases. Below is an example (the database name is returned in 'select dbname'): select dbname, (select count(*) from [dbname].dbo.members) as 'mycount' from databases Can someone help with the syntax of the middle line in the query please? Did you see my response? SignatureHTH Kalen Delaney, SQL Server MVP www.InsideSQLServer.com http://DVD.kalendelaney.com > No, what I'm asking for is how to evaluate the dbName value and use it > in the count part of the query. [quoted text clipped - 79 lines] > Can someone help with the syntax of the middle line in the query > please? i did, do you suggest dynamic execution or sp_MSforeachdb? >Did you see my response? It's up to you. I think the sp_MSforeachdb is easier, but it is not an officially supported feature. And according to the article I pointed you to, dynamic execution has problems of its own. SignatureHTH Kalen Delaney, SQL Server MVP www.InsideSQLServer.com http://DVD.kalendelaney.com > i did, do you suggest dynamic execution or sp_MSforeachdb? > >>Did you see my response? I guess I would say that if this is for your company's own internal use, go with sp_MSforeachdb. If it is part of a commercial product, go with dynamic SQL, but be careful! (i.e. read Erland's article) SignatureHTH Kalen Delaney, SQL Server MVP www.InsideSQLServer.com http://DVD.kalendelaney.com > It's up to you. I think the sp_MSforeachdb is easier, but it is not an > officially supported feature. And according to the article I pointed you [quoted text clipped - 3 lines] >> >>>Did you see my response? There is no way to have a 'where' clause on the query is there? I really need to select the databases on the server, not grab from all of them, because they are not the same structurally. To see what I really need, please read my previous post in this thread with the database examples. There are maybe 30 databases on the server, and I need to only run queries on some of them, based on logins. So the databases selected will be dynamic. The [?].dbo.tablename was exactly the kind of thing I was looking for, but the function doesn't work exactly for my purposes. >---------------------------------------------------------- I guess I would say that if this is for your company's own internal use, go with sp_MSforeachdb. If it is part of a commercial product, go with dynamic SQL, but be careful! (i.e. read Erland's article) SignatureHTH Kalen Delaney, SQL Server MVP www.InsideSQLServer.com http://DVD.kalendelaney.com "Kalen Delaney" <replies@public_newsgroups.com> wrote in message news:%23$tL5mihIHA.3780@TK2MSFTNGP06.phx.gbl... > It's up to you. I think the sp_MSforeachdb is easier, but it is not an > officially supported feature. And according to the article I pointed you [quoted text clipped - 6 lines] >> >>>Did you see my response? Yes, of course you can add a WHERE clause to the SELECT. It is just a normal SELECT using the ? in place of each database name. Alternatively, you could use an IF, since the command is run separately for each database. I didn't understand what exact condition control the databases you're interested in, but the following will run the query for all databases except master, model and tempdb: EXEC sp_MSforeachdb 'if ''?'' not in (''master'', ''model'', ''tempdb'') select ''?'',count(*) from [?].sys.objects' Hopefully, this will give you a start. SignatureHTH Kalen Delaney, SQL Server MVP www.InsideSQLServer.com http://DVD.kalendelaney.com > There is no way to have a 'where' clause on the query is there? I > really need to select the databases on the server, not grab from all [quoted text clipped - 24 lines] >>> >>>>Did you see my response? Thanks. I know you say it's like a normal sql statement, but I am having trouble trying to get more than 1 column returned. i.e. EXEC sp_MSforeachdb 'if ''?'' in (''db1'', ''db2'') select ''?'',count(*) from [?]..sysobjects, select ''?'',count(*) from [?]..sysobjects' ..gives me the error 'Server: Msg 156, Level 15, State 1, Line 2 Incorrect syntax near the keyword 'select'.'....about 70 times. >Yes, of course you can add a WHERE clause to the SELECT. It is just a normal >SELECT using the ? in place of each database name. Alternatively, you could [quoted text clipped - 7 lines] > >Hopefully, this will give you a start. Sorry, posted that in a hurry. What i am trying to do is simply get another column of that count. i.e. EXEC sp_MSforeachdb 'if ''?'' in (''db1'', ''db2'') select ''?'', count(*) from [?]..sysobjects, count(*) from [?]..sysobjects' >Thanks. > [quoted text clipped - 21 lines] >> >>Hopefully, this will give you a start. How are these two count(*) values different from each other? SignatureHTH Kalen Delaney, SQL Server MVP www.InsideSQLServer.com http://DVD.kalendelaney.com > Sorry, posted that in a hurry. > [quoted text clipped - 36 lines] >>> >>>Hopefully, this will give you a start. they aren't. I was just trying to say that if i used the count(*) query to get 1 column, it should work to try and get a second column of the same exact count. I could change the 2nd count to count something different and get an error. My point is, I can't seem to get more than the database name and 1 more column to work. i.e. (doesn't work either with diff. table in 2nd count) EXEC sp_MSforeachdb 'if ''?'' in (''db1'', ''db2'') select ''?'', count(*) from [?]..sysobjects, count(*) from [?]..sysusers' >How are these two count(*) values different from each other? You can only have one FROM clause per statement. But in quotes immediately after the sp_MSforeachdb can be any TSQL batch, which can include multiple statements. Think about what you can do from a query window. Anything that can be execute with a single GO can be in the quotes. So we can have multiple statements EXEC sp_MSforeachdb ' select ''?'', count(*) from [?]..sysobjects; select ''?'',count(*) from [?]..sysusers' If you want to use the IF, keep in mind that to have multiple conditional statements after an IF, you have to use BEGIN/END EXEC sp_MSforeachdb 'if ''?'' in (''db1'', ''db2'') BEGIN select ''?'', count(*) from [?]..sysobjects; select ''?'',count(*) from [?]..sysusers' There is not that much 'special' about sp_MSforeachdb. You first have to make sure you have a correctly written batch, and then just embed it in the quotes. If your batch has its own quotes, you have to remember to double them up. SignatureHTH Kalen Delaney, SQL Server MVP www.InsideSQLServer.com http://DVD.kalendelaney.com > they aren't. I was just trying to say that if i used the count(*) > query to get 1 column, it should work to try and get a second column [quoted text clipped - 9 lines] > >>How are these two count(*) values different from each other? ok thanks. One last question (I hope). If i add a 'where clause' checking against 2 values..i.e.: EXEC sp_MSforeachdb 'if ''?'' in (''db1'', ''db2'') select ''?'' as mydb, count(*) from [?]..mytbl where id1=1 and id2=10' ..it works fine. But if I add one more id to check, it fails, even though I am sure the column is there..i.e.: EXEC sp_MSforeachdb 'if ''?'' in (''db1'', ''db2'') select ''?'' as mydb, count(*) from [?]..mytbl where id1=1 and id2=10 and id3=100' It gives me Invalid column name 'id3', and like I said, I know it's there for the databases in the 'if' part. >You can only have one FROM clause per statement. But in quotes immediately >after the sp_MSforeachdb can be any TSQL batch, which can include multiple [quoted text clipped - 16 lines] >quotes. If your batch has its own quotes, you have to remember to double >them up. i just noticed if i switch id3 to a different column it works. In my case, the 3rd column name is answerid. Is that reserved or something? >ok thanks. One last question (I hope). > [quoted text clipped - 34 lines] >>quotes. If your batch has its own quotes, you have to remember to double >>them up. No, there is nothing special about answerid. Verify that you can run the query as a standalone batch, outside of sp_MSforeachdb. SignatureHTH Kalen Delaney, SQL Server MVP www.InsideSQLServer.com http://DVD.kalendelaney.com > i just noticed if i switch id3 to a different column it works. In my > case, the 3rd column name is answerid. Is that reserved or something? [quoted text clipped - 42 lines] >>>quotes. If your batch has its own quotes, you have to remember to double >>>them up. yes, obviously that was the 1st thing i tried. it's weird, in query analyzer, it does return a data set, but it only flashes for an instant before that error comes up. And in that instant, after running it severl times i can see that the count it comes up with is correct. Just don't know why it goes to the error immediately after. Anyways, thanks for your help. >No, there is nothing special about answerid. Verify that you can run the >query as a standalone batch, outside of sp_MSforeachdb. Unfortunately, you will have to use a cursor on the dbMain.dbo.databases table. For each row, you'll need to generate and execute dynamic SQL to go against the respective DB. Store each execution in a temp table and then select the output from the temp table. Alternatively, you can use tithe cursor to create one query that uses a bunch of UNION ALL's and then execute it. No temp table needed in that case. SignatureTom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS SQL Server MVP Toronto, ON Canada https://mvp.support.microsoft.com/profile/Tom.Moreau No, what I'm asking for is how to evaluate the dbName value and use it in the count part of the query. i.e. i have 3 databases, and i run the query while connected to dbMain: dbMain table: databases data: indx dbName 1 dbOne 2 dbTwo ---------------- dbOne: table: members data: index firstname 1 fred 2 barney ---------------- dbTwo: table: members data: index firstname 1 wilma 2 betty 3 bam-bam 4 pebbles ....and i need the results to be: dbOne 2 dbTwo 4 ..so dbMain is used to store the database names i need to get the member counts from. ..if i use the query: select dbName, (select count(*) from dbOne.dbo.members) as 'mycount' from databases i get: dbOne 2 dbTwo 2 (instead of 4) ..because of the hard-coded database name. >--------------------------------------------------- On Fri, 14 Mar 2008 14:49:08 -0400, "Tom Moreau" <So is there a database named dbname and is there a table in it named member? SignatureTom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS SQL Server MVP Toronto, ON Canada https://mvp.support.microsoft.com/profile/Tom.Moreau <a@b.com> wrote in message news:p4hlt3pauket90l511tmlnn6rmebq19kij@4ax.com... It gives me 'Invalid object name 'dbName.dbo.member'.' So it is not using the record value as the database. Using SQL2K. I don't want to loop through all databases on the server, just the ones returned in a query on the database called 'databases'. On Fri, 14 Mar 2008 13:20:08 -0400, "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote: >Three-part naming is what you are supposed to use and you are doing that. >Could you post your DDL and tell us exactly what the issue is? Hi, I need to query different databases. Below is an example (the database name is returned in 'select dbname'): select dbname, (select count(*) from [dbname].dbo.members) as 'mycount' from databases Can someone help with the syntax of the middle line in the query please? Hi a You can use the undocumented command sp_MSforeachdb EXEC sp_MSforeachdb 'SELECT count(*) as mycount FROM [?].dbo.members' or if you want to add the name of the database, you can use the ? parameter, but it must be in quotes. Since the whole string is already in quotes, you have to use two single quotes: EXEC sp_MSforeachdb 'SELECT ''?'' as mydb, count(*) as mycount FROM [?].dbo.members' If you don't want to use undocumented constructs, you could look at dynamic execution. Please read this article first: http://www.sommarskog.se/dynamic_sql.html SignatureHTH Kalen Delaney, SQL Server MVP www.InsideSQLServer.com http://DVD.kalendelaney.com > Hi, > [quoted text clipped - 7 lines] > Can someone help with the syntax of the middle line in the query > please? DBName must be in the format of [SERVERNAME].[DATABASENAME].DBO.[TABLENAME].. i hope this will help you! > Hi, > [quoted text clipped - 7 lines] > Can someone help with the syntax of the middle line in the query > please? |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.