Check If Database Exists in SQL Server

Question

I am working on project with multiple databases. There is a chance the database may not exists. I am checking to see if the database exists before I run query against the table. The issue is that I am still getting database "Courses" does not exit. I'd like to get rid of that message because I don't want the client to see this message.

DECLARE @dbname nvarchar(128)
SET @dbname = N'Courses'

IF EXISTS (SELECT name 
FROM master.dbo.sysdatabases 
WHERE ('[' + name + ']' = @dbname 
OR name = @dbname))
  BEGIN
    IF NOT EXISTS (SELECT 1 FROM Courses.INFORMATION_SCHEMA.COLUMNS C WHERE C.COLUMN_NAME IN ('isAvailableOnline') AND C.TABLE_NAME IN ('Course'))
    BEGIN
       ALTER TABLE Courses.dbo.Course ADD isAvailableOnline BIT NULL
    END
  END

Aside: Rather than '[' + name + ']' you ought to be using QuoteName to quote an object name. — HABO
– HABO, Commented May 2, 2020 at 3:15
@HABO, thanks, I will definitely change it to use QuoteName instead. — Josiane Ferice
– Josiane Ferice, Commented May 2, 2020 at 3:35
You don't need to bother with that anyway as the name in dbo.sysdatabases will be stored unquoted. And dbo.sysdatabases is deprecated (replaced by sys.databases where it will also be unqouted) — Martin Smith
– Martin Smith, Commented May 2, 2020 at 9:12
@MartinSmith - In this case it appears that the OP is using @dbname as a parameter that may have been quoted, hence the attempt to match it with the value from the table with or without quoting. Yes, they should be using sys.databases. — HABO
– HABO, Commented May 2, 2020 at 13:32
Ah yes, missed that. DB_ID doesn't care if it is quoted or not anyway — Martin Smith
– Martin Smith, Commented May 2, 2020 at 14:12

Martin Smith · Accepted Answer · 2020-05-01 23:16:00Z

4

Wrap the expression using the potentially non existent database in EXEC so it is only compiled if that branch is taken.

I used some shorter idioms for checking database and column existence. If you stick with your existing one for columns it will need to go inside the string that is EXECed with the quotes doubled up to escape them.

DECLARE @dbname sysname
SET @dbname = N'Courses'

IF DB_ID(@dbname) IS NOT NULL
  BEGIN
    IF COL_LENGTH('Courses.dbo.Course','isAvailableOnline') IS NULL
    BEGIN
       EXEC('ALTER TABLE Courses.dbo.Course ADD isAvailableOnline BIT NULL')
    END
  END

answered May 1, 2020 at 23:16

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Check If Database Exists in SQL Server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related