CODEWITHSUNDEEP
t-sqlsql-server-2012
Josiane Ferice
Josiane Ferice

Reputation: 941

Check If Database Exists in SQL Server

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

Upvotes: 2

Views: 6021

Answers (1)

Martin Smith
Martin Smith

Reputation: 454020

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

Upvotes: 4

Related Questions