Solved

How set the database name as variable

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

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 76

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This video discusses moving either the default database or any database to a new volume.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

760 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

20 Experts available now in Live!

Get 1:1 Help Now