Drop a table if it exists before trying to create it

This comes in handy if you need to create an idempotent scripts. For example, a script to create your database’s tables (or other entities) might check for their existence before attempting to create them.

DROP TABLE IF EXISTS [dbo].[sqlstr_Users];
CREATE TABLE [dbo].[sqlstr_Users]
(
	Id int IDENTITY(1,1) NOT NULL,
	UserName VARCHAR(64) NOT NULL,
	FirstName VARCHAR(64) NOT NULL
);

Note that the above DROP TABLE IF EXISTS syntax is available only in SQL Server 2016+. For older versions of SQL Server, you could use the slightly more cumbersome alternative:

IF OBJECT_ID(N'dbo.Employees', N'U') IS NOT NULL DROP TABLE [dbo].[sqlstr_Users];

Here we check whether sqlstr_Users already exists. The OBJECT_ID function returns an internal object ID of the object name passed as a parameter, or NULL. If an ID is returned the table must exist, and so it gets dropped.

Default image
Eric
Code guy.

Leave a Reply