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
2009-03-24 20:05:22
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
2009-03-24 20:09:43
shouldn't "NOT EXISTS" just be "EXISTS"
KM
2009-03-24 20:14:41
why is encourage using INFORMATION_SCHEMA instead of directly using references to tables?
eKek0
2009-03-24 20:17:13
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
2009-03-24 20:21:01
That should have been "format _OF_ INFORMATION_SCHEMA"
mwigdahl
2009-03-24 20:21:37
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
2009-03-24 20:33:40
FYI, REFERENTIAL_CONSTRAINTS is one of the INFORMATION_SCHEMA views..
KM
2009-03-24 20:34:58
I would rather use the less verbose and more abstracted sys.(dot)databases, which is a system view, not table. See below.
ProfK
2009-03-25 15:49:21
+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
2009-03-24 20:21:57
If 'USE [Master]' is inconvenient, you can directly address the view view from any database as 'master.sys.databases'
ProfK
2009-03-25 15:51:10
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
2009-03-25 17:21:27
+1
A:
Actually it's more optimal to use:
if db_id('dms') is not null
--code mine :)
print 'db exists'
Eduardo
2010-01-08 16:19:37