views:

697

answers:

3

What is the ideal way to check if a database exists on a SQL Server using TSQL? It seems multiple approaches to implement this.

+5  A: 

From a Microsoft's script:

DECLARE @dbname nvarchar(128)

SET @dbname = N'Senna'

IF (EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE ('[' + name + ']' = @dbname OR name = @dbname)))

  -- code mine :)

  PRINT 'db exists'
eKek0
That may be from a Microsoft script but it's not Microsoft recommended practice. They encourage using the INFORMATION_SCHEMA views rather than directly accessing the system tables.
mwigdahl
shouldn't "NOT EXISTS" just be "EXISTS"
KM
why is encourage using INFORMATION_SCHEMA instead of directly using references to tables?
eKek0
In general it's because Microsoft commits to the format INFORMATION_SCHEMA, and reserves the right to change the system tables as they please. But in this case, after looking more closely, INFORMATION_SCHEMA doesn't work, so this is probably the best option.
mwigdahl
That should have been "format _OF_ INFORMATION_SCHEMA"
mwigdahl
right from my SQL Server 2005 online docs: REFERENTIAL_CONSTRAINTS (Transact-SQL) This feature has changed from earlier versions of SQL Server. For more information, see Behavior Changes to Database Engine Features in SQL Server 2005.
KM
FYI, REFERENTIAL_CONSTRAINTS is one of the INFORMATION_SCHEMA views..
KM
I would rather use the less verbose and more abstracted sys.(dot)databases, which is a system view, not table. See below.
ProfK
+1  A: 
USE [master]
GO

IF EXISTS (SELECT name FROM sys.databases WHERE name = N'YourDatabaseName')
  Do your thing...

By the way, this came directly from SQL Server Studio, so if you have access to this tool, I recommend you start playing with the various "Script xxxx AS" functions that are available. Will make your life easier! :)

Si
If 'USE [Master]' is inconvenient, you can directly address the view view from any database as 'master.sys.databases'
ProfK
Of course, that is pretty obvious Prof! :) I was dumping what SQL studio generated, as mentioned in the text. I wouldn't have bothered answering otherwise, since Ekeko's answer is almost the same.
Si
+1  A: 

Actually it's more optimal to use:

if db_id('dms') is not null
   --code mine :)
   print 'db exists'
Eduardo