DECLARE @dbname nvarchar(128)
SET @dbname = N'Senna'
IF (EXISTS (SELECT name
FROM master.dbo.databases
WHERE ('[' + name + ']' = @dbname
OR name = @dbname)))
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! :)
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
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
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.
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;"
7条答案
按热度按时间yvgpqqbh1#
Actually, it's best to use:
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.
4dc9hkyq2#
From a Microsoft's script:
pcrecxhr3#
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! :)
jfgube3f4#
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.
Now you can use it like this:
guicsvcw5#
TRY THIS
2vuwiymt6#
Try this if nothing else works. It's recommended by Microsoft .
Change the sales part of the code with the name of the database that you want to delete.
4uqofj5v7#
End Function
''Notice the initial catalog in the connection string must be master! 'Sample Default Connection String