Use Database name

Question

Use Database name

sizal0234

SSCrazy

Points: 2272
More actions
August 7, 2019 at 3:33 am

#3669353

Hi All,
When we open SSMS and see the default database in the available databases field it is sometimes 'master db' ( CTRL + U). Now with the fear of running the script in the wrong db or master db, I usually add the code 'USE DATABASENAME' to my code. example please see below. Now, if the available database is master however will the below code ensure it is executed in the mentioned db. Or there is any possibility or add or change script.
USE DEMODB ( Database name)
--Below code in case if the query has any errors after execution and we can do rollback.
BEGIN TRANSACTION
-ROLLBACK
-COMMIT
INSERT INTO tblstudents VALUES
(34, 'Anna', 'Smth', 'CA')
(35, 'Bob', 'JJonas', 'CA'),
(36, 'Nick', 'JoEas', 'CA');
}

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Andrey Right there with Babe Points: 780 More actions · Answer 1

you can use a simple check like the following:

use [msdb]
go
if db_name()!='master' return 
select getdate()
go

you will not see current date (select getdate()) because the current db is not master and "return" stopped execution of the script

Thomas Rushton SSC-Insane Points: 22648 More actions · Answer 2

You could look at using "SET NOEXEC ON" if you detect that you're in the wrong DB. That'll stop any further code from being executed in the batch (unless one of the statements later is SET NOEXEC OFF, I suppose...)

See https://docs.microsoft.com/en-us/sql/t-sql/statements/set-noexec-transact-sql?view=sql-server-2017

Thomas Rushton
blog: https://thelonedba.wordpress.com

Stamey SSCertifiable Points: 5600 More actions · Answer 3

"Use DBName" is an excellent practice and will serve you well.

This, for instance, will fail at the Use statement if the DB doesn't exist and nothing else will execute.

Use MYDB

Create Table Test (ID Int)

Chris

Learning something new on every visit to SSC. Hoping to pass it on to someone else.

Steve Jones - SSC Editor SSC Guru Points: 734449 More actions · Answer 4

I look at this in two ways. One is that you have scripts to run which are specific to a db (check something, verify something, etc.). In these cases, you definitely can have the USE statement, but since this doesn't prevent other batches from running, you might want to use SET NOEXEC ON.

For changes to structures, you should not be running these manually. Even if you don't have some release software in place, you could create a simple batch file to call SQLCMD and pass in the correct db and pass in your db and file there. Maybe by default make this a tempdb db, which won't mess up other databases.

Having a user to this is fraught with potential issues. Not only that, it relies on the individual to be diligent, and it potentially still has issues with the wrong script running in the wrong environment (prod v qa). I would urge you to start thinking about how to avoid having a DBA/developer run scripts manually on a production environment.

sizal0234 SSCrazy Points: 2272 More actions · Answer 5

Great. Thanks!!

You are right!. Being diligent is extremely important. I even had situations where Developer did NOT mention DB name or commented the DB name and Available DB ( CTRL +U) is set to master or any of system db. Now imagine running 50 plus scripts manually in a day in Prod and you are also thinking about backup jobs and other requests, like being dragged into multiple directions. So do it slowly has worked till now but it is not efficient.

"I would urge you to start thinking about how to avoid having a DBA/developer run scripts manually on a production environment."

This is something which is on my mind and I think power shell should be able to help. However curious to know and discover if there are any standard ways companies follow in situation like this.

Steve Jones - SSC Editor SSC Guru Points: 734449 More actions · Answer 6

Disclosure: I work for Redgate Software

At Redgate, we have various automation tools to help deploy database changes automatically. Other vendors to as well. There are also ways with MIcrosoft using sqlpackage.exe and DACPACs. The easiest way is to save the scripts in an ordered format (01_change.sql, 02_change.sql, etc.), then have a script runner. FlywayDB is one of these frameworks.

Azure DevOps can also make this easy(ier) and orchestrate the changes so that developers don't make silly mistakes.