Solved

How set the database name as variable

Posted on 2010-11-14
13
491 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
 
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
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 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

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

911 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now