如何使用TSQL检查SQL Server上的数据库是否存在?似乎有多种方法可以实现。
如何使用TSQL检查SQL Server上的数据库是否存在?似乎有多种方法可以实现。
实际上,最好使用:
IF DB_ID('dms') IS NOT NULL
--code mine :)
print 'db exists'
请参考https://learn.microsoft.com/en-us/sql/t-sql/functions/db-id-transact-sql,但请注意该方法不适用于Azure SQL Database。
从微软的脚本中:
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...
顺便说一下,这是直接从SQL Server Studio获取的,如果你可以访问这个工具,我建议你开始尝试使用各种可用的“Script xxxx AS”函数。会让你的生活更轻松! :)
我喜欢@Eduardo的回答,也喜欢被采纳的回答。我想从这样的东西中得到一个布尔值,所以我为你们写了这个。
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
现在你可以像这样使用它:
select [dbo].[DatabaseExists]('master') --returns 1
select [dbo].[DatabaseExists]('slave') --returns 0
试一试
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;
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
结束函数
''注意连接字符串中的初始目录必须为master! '示例默认连接字符串
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;"
[master]
特定位置检查数据库名称更安全。 - Anthonydb_id
时,不应该包含[ ]
;那是SQL语法,而不是数据库名称的一部分。 - Jacob Krall