Skip to content
Fix Code Error

How to drop a table if it exists?

March 13, 2021 by Code Error
Posted By: Anonymous

The table name is Scores.

Is it correct to do the following?

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

Solution

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.

Answered By: Anonymous

Related Articles

  • What are the new features in C++17?
  • exclude items based on field value
  • Generate SQL Create Scripts for existing tables with Query
  • How do I include certain conditions in SQL Count
  • Manually raising (throwing) an exception in Python
  • How to find column names for all tables in all…
  • Is there a quick way for checking whether a date…
  • What's the difference between a temp table and table…
  • How to create a temporary table in SSIS control flow…
  • DROP Constraint without knowing the name
  • Pandas resample monthly data into custom frequency…
  • Deprecation warning in Moment.js - Not in a…
  • How to shrink temp tablespace in oracle?
  • Oracle: If Table Exists
  • SQL query return data from multiple tables
  • error LNK2005: ✘✘✘ already defined in…
  • Drawing a simple line graph in Java
  • How can I wrap all BeautifulSoup existing…
  • SQL Server: Query fast, but slow from procedure
  • When should I use a table variable vs temporary…
  • Apache server keeps crashing, "caught SIGTERM,…
  • List of all index & index columns in SQL Server DB
  • Use SQL Server Management Studio to connect remotely…
  • How do I obtain a Query Execution Plan in SQL Server?
  • SQL ELSE block still throws error when IF condition is true
  • Drop all the tables, stored procedures, triggers,…
  • Select SQL Server database size
  • Is it possible to have multiple joins between two…
  • How to pass 2D array (matrix) in a function in C?
  • Conditional WHERE clauses with TVP parameter
  • AppCompat v7 r21 returning error in values.xml?
  • SQL Server query to find all permissions/access for…
  • AWS CLI S3 A client error (403) occurred when…
  • Is it necessary to use # for creating temp tables in…
  • Pandas group-by and sum
  • python 3.2 UnicodeEncodeError: 'charmap' codec can't…
  • How can I get column names from a table in SQL Server?
  • Smart way to truncate long strings
  • Function is Producing an Compile Erorr
  • Cannot connect to SQL Server named instance from…
  • What is a NullReferenceException, and how do I fix it?
  • How to find largest objects in a SQL Server database?
  • How to connect to local instance of SQL Server 2008 Express
  • How can I fix MySQL error #1064?
  • How to change the datetime format in pandas
  • Drop a temporary table if it exists
  • sqldf only returning one row, same query used in SQL
  • Unable to connect to SQL Server instance remotely
  • Adding condition to existing function
  • Creating an index on a table variable
  • In R, prevent unlist from removing NULL values, by…
  • Add unique constraint to combination of two columns
  • How to use temporary table in the from clause in mysql?
  • Can't connect to localhost on SQL Server Express 2012 / 2016
  • Change collations of all columns of all tables in SQL Server
  • Is there a way to get a list of all current…
  • Query to list number of records in each table in a database
  • Best way to extract messy HTML tables using BeautifulSoup
  • How do I drop a foreign key constraint only if it…
  • Nginx no-www to www and www to no-www
  • How do you create a temporary table in an Oracle database?
  • Declaring & Setting Variables in a Select Statement
  • How to check if a stored procedure exists before creating it
  • How to get distinct count of records from table…
  • moment: Array.prototype.some called on null or undefined
  • Recover sa password
  • SQL server query to get the list of columns in a…
  • What is the cause of [Vue warn]: Invalid prop:…
  • How to call Stored Procedure in a View?
  • Java heap terminology: young, old and permanent generations?
  • Handlebars helper - object not working
  • Is there a way to retrieve the view definition from…
  • Create a temporary table in a SELECT statement…
  • How to automate parameters passed into pandas.read_sql?
  • Using IS NULL or IS NOT NULL on join conditions -…
  • Angular: Can't find Promise, Map, Set and Iterator
  • What is an optional value in Swift?
  • T-SQL: Can I query one view for values and then…
  • SQL DROP TABLE foreign key constraint
  • PySpark 3 - UDF to remove items from list column
  • What does "Fatal error: Unexpectedly found nil while…
  • How to loop back to start of question in case of…
  • SELECT records from two tables where rows might not…
  • How to get the start time of a long-running Linux process?
  • Creating a custom counter in Spark based on…
  • Interrupt an earlier timeout event in Simpy
  • Enable remote connections for SQL Server Express 2012
  • How can I drop a table if there is a foreign key…
  • What are the undocumented features and limitations…
  • How do I limit the number of rows returned by an…
  • Oracle PL/SQL - Raise User-Defined Exception With…
  • How do I pass a unique_ptr argument to a constructor…
  • SQL NVARCHAR and VARCHAR Limits
  • Cannot resolve the collation conflict between…
  • slim php get route with parameter (user login system)
  • "Large data" workflows using pandas
  • Is there a performance difference between CTE ,…
  • T-SQL stored procedure that accepts multiple Id values
  • FullCalendar not rendering in polymer project
  • Combining Ember Table with Ember Data

Disclaimer: This content is shared under creative common license cc-by-sa 3.0. It is generated from StackExchange Website Network.

Post navigation

Previous Post:

How to upgrade all Python packages with pip

Next Post:

HTTP GET with request body

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

.net ajax android angular arrays aurelia backbone.js bash c++ css dataframe ember-data ember.js excel git html ios java javascript jquery json laravel linux list mysql next.js node.js pandas php polymer polymer-1.0 python python-3.x r reactjs regex sql sql-server string svelte typescript vue-component vue.js vuejs2 vuetify.js

  • you shouldn’t need to use z-index
  • No column in target database, but getting “The schema update is terminating because data loss might occur”
  • Angular – expected call-signature: ‘changePassword’ to have a typedeftslint(typedef)
  • trying to implement NativeAdFactory imports deprecated method by default in flutter java project
  • What should I use to get an attribute out of my foreign table in Laravel?
© 2022 Fix Code Error