How can I specify in my .sql script that that it only executes against my localhost sql server db instance and not on another open connection to say my integration or test environment?
dbsetupscript.sql
use myDB
--WHAT SAFE GAURD CAN i PUT HERE TO ENSURE THAT THIS SCRIPT CAN ONLY BE EXECUTED VIA MY
-- LOCAL ENVIRONMENT BEFORE I START DELETING TABLES, ETC?
delete MotherTable
-- delete other tables and insert simulated production data
You might try using SQLCMD Mode. You can enable this in SSMS by going to the Query Menu, then selecting SQLCMD Mode.
SQLCMD Mode allows you to define variables, connect to one server run some code, connect to a second server run some code, etc. You can even shell out and run DOS/command-line commands.
You can find some more syntax information here: http://msdn.microsoft.com/en-us/library/ms174187.aspx
Here's a sample to get you started:
-- which server do you want to connect to?
:setvar MyServer ServerName\InstanceName
-- connect to that server
:connect $(MyServer)
-- run your query
select @@servername, @@version
go
try @@ServerName
Code should be something like this
IF (@@ServerName = 'localhost')
BEGIN
delete MotherTable
-- delete other tables and insert simulated production data
END
ELSE
PRINT 'Wrong instance'
if
(
Select CASE when HOST_NAME()='Bummi-Mobi' then 1 else 0 end + Case when DB_NAME()='test' then 1 else 0 end
)<>2
raiserror('Wrong Database', 20, -1) with log
