Check If Database Exists Using T-SQL
If your application uses database, you will probably need to generate it during installation. Before creating of database, you need to check if database with same name already exists on SQL Server. In case of duplicate name, SQL Server will return an error message that database already exists and suggest choosing a different database name.
All database names and some other information are listed in sys.databases table. To check if database exists, you can use this T-SQL code example:
SELECT COUNT(*) FROM sys.databases WHERE [Name] = 'Northwind'
This example checks if database with the name "Northwind" exists on server. If database exists, SQL query will return 1, if not exists it will return 0. You can change Northwind to other name to see how it works. To automate things, you can create stored procedure that takes database name as a parameter. Here is the code:
CREATE PROCEDURE IsDbExists
@DatabaseName varchar(50)
AS
BEGIN
SELECT COUNT(*) FROM sys.databases WHERE [Name] = @DatabaseName
END
GO
Now, you can easily check if database exists without need to remember syntax:
IsDbExists 'Northwind'
Notice that Microsoft reserves right to change system tables in some future version of SQL Server. Because of that, now is recommended to use specialized function DB_ID(). Here is example use:
-- Returns ID of selected database
SELECT DB_ID('Northwind')
This SQL query returns ID (type of int) of selected database. If database doesn't exist it will return NULL. This is probably good enough for most cases, but if you want to get 1 if database exists and get 0 if not, you can use the same logic as in first example:
SELECT COUNT(*) FROM (SELECT DB_ID('Northwind') AS DbID) t WHERE DbID IS NOT NULL
Or, to wrap it into procedure:
CREATE PROCEDURE IsDbExists
@DatabaseName varchar(50)
AS
BEGIN
SELECT COUNT(*) FROM (SELECT DB_ID(@DatabaseName) AS DbID) t WHERE DbID IS NOT NULL
END
GO
As you see, it is pretty simple to check if database already exists. Then, you can drop existing database prior of creating new version or suggest user to choose other name. Happy coding!
Related articles:
1. T-SQL Reserved Words List
2. Solved: Create Failed For User 'UserName' Error
3. How To Set Multiple Variables By Using SELECT In Stored Proc
