What is the ideal way to check if a database exists on a SQL Server using TSQL? It seems multiple approaches to implement this.
Add a comment
|
7 Answers
Actually, it's best to use:
IF DB_ID('dms') IS NOT NULL
--code mine :)
print 'db exists'
See https://learn.microsoft.com/en-us/sql/t-sql/functions/db-id-transact-sql and note that this does not make sense with the Azure SQL Database.
13 Comments
Mike K
Well it's certainly shorter and more cryptic. Out of curiosity, why is it better?
Anthony
Presumably because db_id is safer than checking for a database name in a specific location in
[master]
Eduardo
Well, yeah, plus that it is nearly impossible for db_id() to be worse (could be the same complexity/cost) than the accepted answer since db_id queries for a number. So I rather bet on db_id() being implemented in a smarter way, since it was done by the database developers.
Jason Foglia
If you have permission issues, like you don't have permission to access [master] this works well!
Jacob Krall
@MadTigger: you shouldn't include
[ ] in your call to db_id; that's SQL syntax, not part of the database name. |
From a Microsoft's script:
DECLARE @dbname nvarchar(128)
SET @dbname = N'Senna'
IF (EXISTS (SELECT name
FROM master.dbo.databases
WHERE ('[' + name + ']' = @dbname
OR name = @dbname)))
12 Comments
mwigdahl
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.
eKek0
why is encourage using INFORMATION_SCHEMA instead of directly using references to tables?
mwigdahl
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.
granadaCoder
I agree INFORMATION_SCHEMA is preferred for checking objects ~inside a database. But can INFORMATION_SCHEMA to used to check for the db itself? <<<<< ............... CHECK_CONSTRAINTS Check Constraints COLUMN_DOMAIN_USAGE Every column that has a user-defined data type. COLUMN_PRIVILEGES Every column with a privilege granted to or by the current user in the current database. COLUMNS Lists every column in the system CONSTRAINT_COLUMN_USAGE Every column that has a constraint defined on it. CONSTRAINT_TABLE_USAGE Every table that has a constraint defined on it.
Martin Smith
@mwigdahl - Please provide a reference for this claimed recommended practice.
|
IF EXISTS (SELECT name FROM master.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! :)
1 Comment
ProfK
If 'USE [Master]' is inconvenient, you can directly address the view view from any database as 'master.sys.databases'
I like @Eduardo's answer and I liked the accepted answer. I like to get back a boolean from something like this, so I wrote it up for you guys.
CREATE FUNCTION dbo.DatabaseExists(@dbname nvarchar(128))
RETURNS bit
AS
BEGIN
declare @result bit = 0
SELECT @result = CAST(
CASE WHEN db_id(@dbname) is not null THEN 1
ELSE 0
END
AS BIT)
return @result
END
GO
Now you can use it like this:
select [dbo].[DatabaseExists]('master') --returns 1
select [dbo].[DatabaseExists]('slave') --returns 0
Comments
TRY THIS
IF EXISTS
(
SELECT name FROM master.dbo.sysdatabases
WHERE name = N'New_Database'
)
BEGIN
SELECT 'Database Name already Exist' AS Message
END
ELSE
BEGIN
CREATE DATABASE [New_Database]
SELECT 'New Database is Created'
END
1 Comment
gchq
This was certainly the best solution for me - one upvote
Try this if nothing else works. It's recommended by Microsoft.
USE tempdb;
GO
DECLARE @SQL nvarchar(1000);
IF EXISTS (SELECT 1 FROM sys.databases WHERE [name] = N'Sales')
BEGIN
SET @SQL = N'USE [Sales];
ALTER DATABASE Sales SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
USE [tempdb];
DROP DATABASE Sales;';
EXEC (@SQL);
END;
Change the sales part of the code with the name of the database that you want to delete.
Comments
Public Function SQLDatabaseExist(ByVal DefaultConnectionString As String, ByVal DataBaseName As String) As Boolean
Try
'CREATE DATABASE
Dim SqlString As String = ""
SqlString = "SELECT CASE WHEN EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'" & DataBaseName & "') THEN CAST (1 AS BIT) ELSE CAST (0 AS BIT) END"
Dim ExcRet As Integer = 0
Using connection As New SqlConnection(DefaultConnectionString)
Dim command As New SqlCommand(SqlString, connection)
command.Connection.Open()
ExcRet = command.ExecuteScalar()
command.Connection.Close()
command.Dispose()
End Using
Return ExcRet
Catch ex As Exception
Return False
End Try
End Function
Notice the initial catalog in the connection string must be master!
'Sample Default Connection String
Dim DefaultConnectionString As String = "Data Source=localhost\SQLSERVER2008;Initial Catalog=Master; User ID=SA; Password='123123'; MultipleActiveResultSets=false; Connect Timeout=15;Encrypt=False;Packet Size=4096;"
2 Comments
Will Blair
This answer is not relevant to the original question which is 14 years old and has an accepted answer.
Reagan
It is useful for other people who code vb.net