Solved

How set the database name as variable

Posted on 2010-11-14
13
499 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
  • 5
  • 5
  • 2
  • +1
13 Comments
 
LVL 77

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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 77

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 500 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

803 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