How to drop a table if it exists

Question

The table name is Scores   Is it correct to do the following   IF EXISTS SELECT             FROM   dbo Scores    DROP TABLE dbo Scores

User · Answer

Simple is that   IF OBJECT ID dbo TableName   U   IS NOT NULL DROP TABLE dbo TableName   where dbo TableName is your desired table and  U  is type of your table

User · Answer

I use   if exists  select               from sys tables             where name    tableName              and schema id   schema id  dbo    begin     drop table dbo tableName end

User · Answer

I hope this helps   begin try drop table  tempTable end try begin catch end catch

User · Answer

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

User · Answer

The ANSI SQL cross-platform way is to use the INFORMATION SCHEMA  which was specifically designed to query meta data about objects within SQL databases   if exists  select   from INFORMATION SCHEMA TABLES where TABLE NAME    Scores  AND TABLE SCHEMA    dbo       drop table dbo Scores    Most modern RDBMS servers provide  at least  basic INFORMATION SCHEMA support  including  MySQL  Postgres  Oracle  IBM DB2  and Microsoft SQL Server 7 0  and greater

User · Answer

Have seen so many that don t really work  when a temp table is created it must be deleted from the tempdb   The only code that works is   IF OBJECT ID  tempdb   tempdbname   IS NOT NULL     --Remove dbo here      DROP TABLE  tempdbname   -- Remoeve  tempdb dbo

User · Answer

Do like this  it is the easiest way   qry will be your own query  whatever you want in the select list   set  qry     select   into TempData from       qry     Tmp     exec   qry   select   from TempData   drop table TempData

User · Answer

From SQL Server 2016 you can use   DROP TABLE IF EXISTS dbo Scores   Reference  DROP IF EXISTS - new thing in SQL Server 2016  It will be in SQL Azure Database soon

User · Answer

IF EXISTS  SELECT NAME FROM SYS OBJECTS WHERE object id   OBJECT ID N Scores   AND TYPE in  N U        DROP TABLE Scores GO

User · Answer

A better visual and easy way  if you are using Visual Studio  just open from menu bar      View -  SQL Server Object Explorer   it should open like shown here    Select and Right Click the Table you wish to delete  then delete  Such a screen should be displayed  Click Update Database to confirm     This method is very safe as it gives you the feedback and will warn of any relations of the deleted table with other tables

User · Answer

I wrote a little UDF that returns 1 if its argument is the name of an extant table  0 otherwise   CREATE FUNCTION  dbo   Table exists         TableName VARCHAR 200        RETURNS BIT AS BEGIN     If Exists select   from INFORMATION SCHEMA TABLES where TABLE NAME    TableName          RETURN 1       RETURN 0  END  GO   To delete table User if it exists  call it like so   IF  dbo   Table exists   User     1 Drop table  User

User · Answer

There is an easier way  DROP TABLE IF EXISTS table name

User · Answer

Make sure to use cascade constraint at the end to automatically drop all objects that depend on the table  such as views and projections   drop table if exists tableName cascade

User · Answer

In SQL Server 2016  13 x  and above  DROP TABLE IF EXISTS dbo Scores   In earlier versions  IF OBJECT ID  dbo Scores    U   IS NOT NULL  DROP TABLE dbo Scores     U is your table type

User · Answer

Or   if exists  select   from sys objects where name    Scores  and type    u       drop table Scores

[sql-server] How to drop a table if it exists?

Examples related to sql-server

Examples related to tsql