Drop If Exists VS Drop

DROP IF EXISTS VS DROP?

Standard SQL syntax is

DROP TABLE table_name;

IF EXISTS is not standard; different platforms might support it with different syntax, or not support it at all. In PostgreSQL, the syntax is

DROP TABLE IF EXISTS table_name;

The first one will throw an error if the table doesn't exist, or if other database objects depend on it. Most often, the other database objects will be foreign key references, but there may be others, too. (Views, for example.) The second will not throw an error if the table doesn't exist, but it will still throw an error if other database objects depend on it.

To drop a table, and all the other objects that depend on it, use one of these.

DROP TABLE table_name CASCADE;
DROP TABLE IF EXISTS table_name CASCADE;

Use CASCADE with great care.

DROP IF EXISTS VS DROP?

Standard SQL syntax is

DROP TABLE table_name;

IF EXISTS is not standard; different platforms might support it with different syntax, or not support it at all. In PostgreSQL, the syntax is

DROP TABLE IF EXISTS table_name;

The first one will throw an error if the table doesn't exist, or if other database objects depend on it. Most often, the other database objects will be foreign key references, but there may be others, too. (Views, for example.) The second will not throw an error if the table doesn't exist, but it will still throw an error if other database objects depend on it.

To drop a table, and all the other objects that depend on it, use one of these.

DROP TABLE table_name CASCADE;
DROP TABLE IF EXISTS table_name CASCADE;

Use CASCADE with great care.

How to drop a table if it exists?

Is it correct to do the following?

IF EXISTS(SELECT *
          FROM   dbo.Scores)
  DROP TABLE dbo.Scores

No. That will drop the table only if it contains any rows (and will raise an error if the table does not exist).

Instead, for a permanent table you can use

IF OBJECT_ID('dbo.Scores', 'U') IS NOT NULL 
  DROP TABLE dbo.Scores; 

Or, for a temporary table you can use

IF OBJECT_ID('tempdb.dbo.#TempTableName', 'U') IS NOT NULL
  DROP TABLE #TempTableName; 

SQL Server 2016+ has a better way, using DROP TABLE IF EXISTS …. See the answer by @Jovan.

DROP TABLE IF EXISTS vs OBJECT_ID IS NOT NULL

Explanation

They do the same thing just different syntax, and the later method in your question is newer. The IF EXISTS clause has been supported with DROP TABLE since SQL Server 2016 13.x up through the current version as of writting this, SQL Server 2019 (15.x).

Documentation

The IF EXISTS functionality is documented in the arguments section here: https://learn.microsoft.com/en-us/sql/t-sql/statements/drop-table-transact-sql?view=sql-server-ver15#arguments

What's the difference between creating a table if it doesn't exist, and dropping the tables if they exist before creating them?

Generally when releasing db updates, it get used to compose sql scripts with statements for creating tables or other objects.

From here you may follow several strategies. Mysql seems to support also the strategy of creating one script file that you append with the updates, and so you will create the table only the first time you exec the script, all the following executions won't create the tables. Of course in this scenario you will have add the "if not exists" clause to all objects of your script file.

Instead, a more followed strategy is to create several files as many as are the db objects, one file per object. And so, if the object is a table, then first to create the table you have drop it if exists, and so explained the other statement.

Of course, when you execute this table script files you have pay close attention at the db data you want preserve from lost, and also to the order of their execution due to eventual foreign key constraints that may affects the dropping operations.

Generally this scripts are launched only in dev or test environments or when you are installing first time your db in production.

In prod env usually, as far as updating db table objects, get launched scripts that contain only alter statements.

Drop view if exists

your exists syntax is wrong and you should seperate DDL with go like below

if exists(select 1 from sys.views where name='tst' and type='v')
drop view tst;
go

create view tst
as
select * from test

you also can check existence test, with object_id like below

if object_id('tst','v') is not null
drop view tst;
go

create view tst
as
select * from test

In SQL 2016,you can use below syntax to drop

Drop view  if exists dbo.tst

From SQL2016 CU1,you can do below

create or alter view vwTest
as
 select 1 as col;
go

If table exists drop table then create it, if it does not exist just create it

Just put DROP TABLE IF EXISTS `tablename`; before your CREATE TABLE statement.

That statement drops the table if it exists but will not throw an error if it does not.

Correct way to drop a table in SQL Server

The second method will throw an error if the table doesn't exist, or if other database objects depend on it.
The first one will not throw an error if the table doesn't exist, but it will still throw an error if other database objects depend on it.

Check out this answers on Stack-overflow:

• DROP IF EXISTS VS DROP?
• How to drop a table if it exists?

SQL Server - drop multiple columns with IF EXISTS at once

The syntax is a bit cumbersome but

ALTER TABLE t DROP COLUMN IF EXISTS col1, 
                   COLUMN IF EXISTS col2;

works fine

---

Related Topics