I am writing a sql script for sql server 2008 where I place a use statement at the beginning that specifies the database the script should be run against:
use [my_database]
As I have different environments where the same database exists, eg dev, qa, prod, is there any way I can specify in the script the environment the script is for, either by server name or ip address or by any other mechanism.
You can put the SQL Management Studio in SQLCMD mode and specify the server with the :CONNECT myserver statement.
You can switch on command mode by clicking on the option in the pic below
Your script would then look something like this
:CONNECT devserver
use [my_database]
SELECT * FROM my_table
You can even make the query window switch servers during execution
:CONNECT devserver
use [my_database]
SELECT * FROM my_table
GO
:CONNECT uatserver
use [my_database]
SELECT * FROM my_table
To connect with a specific user and password you can specify this as follows
:CONNECT devserver -U myUser -P myPassword
use [my_database]
SELECT * FROM my_table
There are actually a number of options you can specify which are well documented on msdn.
:CONNECT devserver -U user -P password. I will add it to the answer once my updates stop erroring.
That's a CONNECTION setting, not a parameter within the script.
You can run the same script in different environments via batch file or powershell script if desired, or you could set up linked servers, but you can't just say
USE SomeOtherServer
There are security and networking implications as well.
Assuming you will run all of these scripts on a particular server - e.g. the Dev server - then you merely need to create a Linked Server to each of the other servers.
Then, for example, you could run an identically named stored procedure on each of these servers thusly:
EXEC MyDatabase.dbo.mysp_DoSomething --Dev Server; no server prefix needed since that's where we are
EXEC QA.MyDatabase.dbo.mysp_DoSomething --QA Server
EXEC Prod.MyDatabase.dbo.mysp_DoSomething --Prod server
etc.
