|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
USE statementthe USE statement. For example, USE pubs. How do I do that dynamically? For example, a stored procedure is executing in the pubs database. Based on a variable (either passed in or determined through logic), I need to change the database context to Northwind. T-SQL doesn't support USE @MyDatabase. Is there a system stored procedure or some other trick to change the context dynamically to another database? What I'm trying to do is develop an Admin database where I have stored procedures, functions, etc. that will execute DBCC commands such as DBCC SHOW_STATISTICS on a specified database without having to hard code database names. (The DBCC SHOW_STATISTICS does not support a fully qualified object name such as 'pubs.dbo.titles' so I need to be that context and I don't want to store all of my admin objects in every database as that would be a maintenance nightmare.) Thanks, Joe EXEC('USE '+@MyDatabase+'; DBCC SHOW_STATISTICS');
Show quote "Joe from WI" <Joefro***@discussions.microsoft.com> wrote in message news:94F2B3F9-D2BA-4988-BA9F-E7D2D869587B@microsoft.com... > In a script or stored procedure, you can change your database context with > the USE statement. For example, USE pubs. > > How do I do that dynamically? > > For example, a stored procedure is executing in the pubs database. Based > on > a variable (either passed in or determined through logic), I need to > change > the database context to Northwind. T-SQL doesn't support USE @MyDatabase. > > Is there a system stored procedure or some other trick to change the > context > dynamically to another database? > > What I'm trying to do is develop an Admin database where I have stored > procedures, functions, etc. that will execute DBCC commands such as DBCC > SHOW_STATISTICS on a specified database without having to hard code > database > names. (The DBCC SHOW_STATISTICS does not support a fully qualified > object > name such as 'pubs.dbo.titles' so I need to be that context and I don't > want > to store all of my admin objects in every database as that would be a > maintenance nightmare.) > > Thanks, > Joe Thanks Aaron. I tried the exec ('use ' + @MyDatabase) but I never thought to
concatenate the DBCC command onto that. THANKS!!! Now that I think about it...it makes sense because the exec is probably run nested so when it ends so does the change in database context. And, my statements that followed would execute in the wrong database. Thanks Abhishek. But hardcoding the database names is not an option. And you are right, exec ('use ' + @MyDatabase) is not retained after execution resumes in the stored procedure. Aaron got it right...I need to concatenate the statements so that it is all executed in one nested execution. Thanks Anith. (See my reply to Aaron.) In addition, I never thought to use the sp_ExecuteSQL in that manner. For my use, I'd have to concatenate a string together so that the database name is dynamic and then EXEC (@MySQL). THANKS for that tip on sp_ExecuteSQL. Thanks again everybody. You're time and assistance is greatly appreciated. Joe Show quote "Aaron Bertrand [SQL Server MVP]" wrote: > EXEC('USE '+@MyDatabase+'; DBCC SHOW_STATISTICS'); > > > > "Joe from WI" <Joefro***@discussions.microsoft.com> wrote in message > news:94F2B3F9-D2BA-4988-BA9F-E7D2D869587B@microsoft.com... > > In a script or stored procedure, you can change your database context with > > the USE statement. For example, USE pubs. > > > > How do I do that dynamically? > > > > For example, a stored procedure is executing in the pubs database. Based > > on > > a variable (either passed in or determined through logic), I need to > > change > > the database context to Northwind. T-SQL doesn't support USE @MyDatabase. > > > > Is there a system stored procedure or some other trick to change the > > context > > dynamically to another database? > > > > What I'm trying to do is develop an Admin database where I have stored > > procedures, functions, etc. that will execute DBCC commands such as DBCC > > SHOW_STATISTICS on a specified database without having to hard code > > database > > names. (The DBCC SHOW_STATISTICS does not support a fully qualified > > object > > name such as 'pubs.dbo.titles' so I need to be that context and I don't > > want > > to store all of my admin objects in every database as that would be a > > maintenance nightmare.) > > > > Thanks, > > Joe > > > Joe
a trival solution would be to do if ....else.... thing and check your context that way to switch to a different db. say for example if(1=2) use pubs else use northwind I dont believe you can do this with dynamic SQL by doing something like exec('use '+@dbname) but you might wanna try. As far as i know it doesnt work and your code will end up staying in the same db. I wonder if there is any other system sproc to do that. Hope the above helps Abhishek Show quote "Joe from WI" wrote: > In a script or stored procedure, you can change your database context with > the USE statement. For example, USE pubs. > > How do I do that dynamically? > > For example, a stored procedure is executing in the pubs database. Based on > a variable (either passed in or determined through logic), I need to change > the database context to Northwind. T-SQL doesn't support USE @MyDatabase. > > Is there a system stored procedure or some other trick to change the context > dynamically to another database? > > What I'm trying to do is develop an Admin database where I have stored > procedures, functions, etc. that will execute DBCC commands such as DBCC > SHOW_STATISTICS on a specified database without having to hard code database > names. (The DBCC SHOW_STATISTICS does not support a fully qualified object > name such as 'pubs.dbo.titles' so I need to be that context and I don't want > to store all of my admin objects in every database as that would be a > maintenance nightmare.) > > Thanks, > Joe >> I dont believe you can do this with dynamic SQL Dynamic SQL is simpler that other methods, the OP could use EXEC directly as Aaron recommended or use sp_ExecuteSQL: EXEC('USE pubs; DBCC SHOW_STATISTICS (authors, UPKCL_auidind)') ; EXEC pubs.dbo.sp_ExecuteSQL N'DBCC SHOW_STATISTICS (authors, UPKCL_auidind)' ; -- Anith Sen:
with the exec stmt for dynamic SQL you can just run one query or DBCC or wahtever is required in context of that db. Maybe i didnt understood the OP's requirements properly. Yes if he just have to change the context of DB for his DBCC then it can be done with Dynamic SQL but if he has a set of things to be performed in that db then exec('use ' +@DbName) wont work. it should be noted that the above works differenly than simply saying use pubs;GO which would change the working db to pubs. where as the dynamic will do it only within the context of exec. Abhishek Show quote "Anith Sen" wrote: > >> I dont believe you can do this with dynamic SQL > > Dynamic SQL is simpler that other methods, the OP could use EXEC directly as > Aaron recommended or use sp_ExecuteSQL: > > EXEC('USE pubs; DBCC SHOW_STATISTICS (authors, UPKCL_auidind)') ; > EXEC pubs.dbo.sp_ExecuteSQL N'DBCC SHOW_STATISTICS (authors, UPKCL_auidind)' > ; > > -- > Anith > > > > Maybe i didnt understood the OP's requirements properly. Yes if he just Why not?> have > to change the context of DB for his DBCC then it can be done with Dynamic > SQL > but if he has a set of things to be performed in that db then exec('use ' > +@DbName) wont work. EXEC('USE '+@dbname+'; DBCC SHOW_STATISTICS; DBCC CHECKDB'); > which would change the working db to pubs. where as the dynamic will do it Yep, and you can put more than one statement inside of a single EXEC(). > only within the context of exec. Same with sp_executeSQL. Thanks for the advice aaron and Anith.
Hey i learned something today. afterall thats whay i am here.. Show quote "Aaron Bertrand [SQL Server MVP]" wrote: > > Maybe i didnt understood the OP's requirements properly. Yes if he just > > have > > to change the context of DB for his DBCC then it can be done with Dynamic > > SQL > > but if he has a set of things to be performed in that db then exec('use ' > > +@DbName) wont work. > > Why not? > > EXEC('USE '+@dbname+'; DBCC SHOW_STATISTICS; DBCC CHECKDB'); > > > which would change the working db to pubs. where as the dynamic will do it > > only within the context of exec. > > Yep, and you can put more than one statement inside of a single EXEC(). > Same with sp_executeSQL. > > >  |
|||||||||||||||||||||||
Other interesting topics