[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

How set the database name as variable

Posted on 2010-11-14
13
Medium Priority
?
541 Views
Last Modified: 2012-05-10
I'm looking the way to declare a database name in a variable, then all the queries can be updated just replacing the name in the variable.

Any ideas?
0
Comment
Question by:moralesrd
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
  • 2
  • +1
13 Comments
 
LVL 80

Expert Comment

by:arnold
ID: 34131866
Could you provide details on which language you are using to access the database.

Your query can have a variable i.e. select * from `$table_name_variable`
I.e. you are looking at dynamic queries?

http://www.databasejournal.com/features/mssql/article.php/1438931/Dynamic-SQL-Beginners-Guide.htm

0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34131880
In what particular scenario are you thinking?

The only ways I can think of to do this would be dynamic sql or OPENQUERY or EXECUTE () . ON <SERVER> which would all require turning all your T-SQL into strings, and there will be extra work required if your T-SQL includes things like function creation.
0
 

Author Comment

by:moralesrd
ID: 34131966
I'm using MS SQL 2005.... that I'm trying to do is something like this:
select * from MyDatabase.dbo.MyTable Alias
select * from MyDatabase.dbo.MyTable2 Alias2
select * from MyDatabase.dbo.MyTable3 Alias3

Note: MyDatabase is used in many queries inside of my sql file, and needs to be changed constantly to manage different databases, so I would like to use something like:

declare @MyDBName vachar(25)
set @MyDBName = 'MyDatabase'
select * from @MyDBName.dbo.MyTable Alias

I'm not sure if there such option possible, so, any idea that could help...
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 80

Expert Comment

by:arnold
ID: 34132015
I think this is how and what the link on dynamic SQL I provided deals with.
not sure what the point of your use of the Alias as it is only valid during the execution of the query and no where else.

You could have an array of databases and then reference them that way to avoid the multiple declare/set mechanism.
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34132025
Like I said, I'm pretty sure it is not possible for more complicated stuff, but for the easy ones, you could use

use MyDatabase -- change it just here

select * from dbo.MyTable Alias
select * from dbo.MyTable2 Alias2
select * from dbo.MyTable3 Alias3

Open in new window


or

declare @MyDBName varchar(25)
set @MyDBName = 'MyDatabase'
declare @sql nvarchar(max)
set @sql = replace('  -- your whole query after this line
select * from :dbname:.dbo.MyTable Alias
select * from :dbname:.dbo.MyTable2 Alias2 where txtCol like ''A%''  -- example of doubling up
select * from :dbname:.dbo.MyTable3 Alias3
'  -- to before this line. double up any quotes
, ':dbname:', @MyDBName)
exec (@sql)

Open in new window


But once it includes things like create proc, something more complicated is required.
0
 

Author Comment

by:moralesrd
ID: 34133100
With the option b, is possible to add multiple databases?
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34133179
Sure, something like this
declare @MyDBName varchar(25), @MyDBName2 varchar(25)
select @MyDBName = 'MyDatabase', @MyDBName2 = 'DB2'
declare @sql nvarchar(max)
set @sql = replace(replace('  -- your whole query after this line
select * from :dbname:.dbo.MyTable Alias
select * into :db2:..othertable1 from :dbname:.dbo.MyTable2 Alias2 where txtCol like ''A%''  -- example of doubling up
select * from :dbname:.dbo.MyTable3 Alias3
'  -- to before this line. double up any quotes
, ':dbname:', @MyDBName)
, ':db2:', @MyDBName2)
exec (@sql)

Open in new window

The key really is that you are building a template TSQL string, and using the REPLACE to swap in some variables.
Here I used :dbname: and :db2: (wrapped in : to make it look unique, normally your sql shouldn't have anything looking like that so it is safe).
0
 
LVL 3

Expert Comment

by:krsreddy5
ID: 34133928
Hi you can use sp_msforeachdb undocumented stored procedure to execute the set of T-SQL commands against each database on the server.

sp_MSforeachdb
'USE ?
IF DB_ID(''?'')  in (5,6) -- you can add multiple Database IDs seperated by a cama, where you want execute the cmmoands against the database(s)
BEGIN
select db_name()   -- you can replace your select statements here
end'

K. RajaSekhar Reddy
www.dbaarticles.com
FTTDBAS@dbaarticles.com
0
 

Author Comment

by:moralesrd
ID: 34151886
I'm trying to generate an script using your settings, however I'm getting the error:

Must declare the scalar variable "@MyDBName".


Any ideas?
0
 

Author Comment

by:moralesrd
ID: 34151902
krsreddy5, could youu provide an example with an small quey included?
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34151934
> I'm trying to generate an script using your settings, however I'm getting the error:
> Must declare the scalar variable "@MyDBName".

What query did you use exactly?
0
 

Author Comment

by:moralesrd
ID: 34152017
DECLARE @MyDBName varchar(25),      @MyDBName2 varchar(25)
select @MyDBName = 'DB1', @MyDBName2 = 'DB2'
declare @sql nvarchar(max)
set @sql = replace(replace('
select tb1.var3 from       
      @MyDBName.dbo.table1 tb1,
      @MyDBName2.dbo.table2 tb2
where      tb1.var = tb2.var

'  -- to before this line. double up any quotes
, ':dbname:', @MyDBName)
, ':db2:', @MyDBName2)
exec (@sql)
 
------

0
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 2000 total points
ID: 34152215
DECLARE @MyDBName varchar(25),      @MyDBName2 varchar(25)
select @MyDBName = 'DB1', @MyDBName2 = 'DB2'
declare @sql nvarchar(max)
set @sql = replace(replace('
select tb1.var3 from      
      :dbname:.dbo.table1 tb1,
      :db2:.dbo.table2 tb2
where      tb1.var = tb2.var

'  -- to before this line. double up any quotes
, ':dbname:', @MyDBName)
, ':db2:', @MyDBName2)
exec (@sql)
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question