There is various ways to check database is exists or not in SQL Server. I am sharing with you below Queries tested by me.
Scenario 1:-
DECLARE @DBName NvarChar(200)
SET @DBName = N'Your_Database_Name'
IF EXISTS(SELECT name FROM master.dbo.sysdatabases
WHERE ('[' + name + ']' = @DBName OR name = @DBName))
BEGIN
PRINT 'Database Exists';
END
ELSE
BEGIN
--You can write Database Create Statement Here.
PRINT 'Database Created'
END
Note:- If you have permission issues, like you don't have permission to access [master] this works properly.
Scenario 2:-
A DB_ID function [See https://docs.microsoft.com/en-us/sql/t-sql/functions/db-id-transact-sql]Syntax :
DB_ID ( [ 'database_name' ] )
Here 'database_name', The name of the database whose database ID number DB_ID will return.If the call to DB_ID omits database_name, DB_ID returns the ID of the current database.
Return types :
Int
Examples : Returning the database ID of the current database
SELECT DB_ID() AS [Database ID]
Go
Returning the database ID of a specified databaseSELECT DB_ID(N'Your_Database_Name') AS [Database ID] Go
I have used above function like below :
IF (DB_ID(N'Your_Database_Name') IS NOT NULL) BEGIN PRINT 'Database Exists'; END ELSE BEGIN --You can write Database Create Statement Here. PRINT 'Database Created' END
Scenrio 3: We can also create a function using DB_ID() as below
Example :
CREATE FUNCTION dbo.DatabaseExists(@DBName NvarChar(100)) 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
-- We can use above function like below:
Select [dbo].[DatabaseExists]('master') AS DatabaseExists --Returns 1 Select [dbo].[DatabaseExists]('Test') AS DatabaseExists --Returns 0
Scenrio 4: There is another simple way is available to check as below:
SELECT * FROM master.sys.databases WHERE name= N'Your_Database_Name'
Example: I am using this query in my way as below:
IF EXISTS(SELECT name FROM master.sys.databases WHERE name= N'Your_Database_Name') BEGIN PRINT 'Database Exists'; END ELSE BEGIN --You can write Database Create Statement Here. PRINT 'Database Created' END
Scenrio 5: There is another simple way is available to check as below:
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name= N'Your_Database_Name') BEGIN PRINT 'Database Exists'; END ELSE BEGIN --You can write Database Create Statement Here. --CREATE DATABASE [Your_Database_Name] PRINT 'Database Created' END
No comments:
Post a Comment