Errors warning that “there is already an object” and that the “database already exists” can easily be avoided by first determining if your table and database have already been created.
Does these types of error annoys you when you’re trying to create tables and database in SQL Server?
You will never get these messages if you first check that the database and table exists through the IF EXISTS command. Here are two examples searching for a specific table or database.
Does the table exist in the database?
IF EXISTS ( SELECT [name] FROM sys.tables WHERE [name] = 'Employee' )
DROP TABLE Employee
GO
CREATE TABLE Employee
(
EmpId INT,
FName VARCHAR(30),
LName VARCHAR(30),
Salary INT
)
GO
In this example sys.tables is a system created table which includes the list of all the created tables within your database. First, we filter with the name column and specify the employee table name. If this table already exists, we drop it and create a new employee table.
If the table does not exist, the employee table statement.
Does the database exist?
We will walk through in a pattern similiar to our previous example. Instead of checking for a table, we will check if the db exists instead.
IF EXISTS ( SELECT [name] FROM sys.databases WHERE [name] = 'DbArticles' )
DROP DATABASE DbArticles
GO
CREATE DATABASE DbArticles
GO
The sys.databases is a system created table which includes the list of databases within the SQL server. We filter based on the name column and the dArticles database name. If the database already exists, we then drop the database and create a new database under the same name. If the database does not exist, it will create dArticles database.
