Solved

How set the database name as variable

Posted on 2010-11-14
13
506 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
In this article I will describe the Copy Database Wizard 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.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

839 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