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.

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.

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).

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"

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

Or:

if exists (select * from sys.objects where name = 'Scores' and type = 'u')
    drop table Scores

There is an easier way

DROP TABLE IF EXISTS table_name;

I hope this helps:

begin try drop table #tempTable end try
begin catch end catch

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]

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.

IF EXISTS (SELECT NAME FROM SYS.OBJECTS WHERE object_id = OBJECT_ID(N'Scores') AND TYPE in (N'U'))
    DROP TABLE Scores
GO

I use:

if exists (select * 
           from sys.tables 
           where name = 'tableName' 
           and schema_id = schema_id('dbo'))
begin
    drop table dbo.tableName
end

SQL Server 2016 and above the best and simple one is DROP TABLE IF EXISTS [TABLE NAME]

Ex:

DROP TABLE IF EXISTS dbo.Scores

if suppose the above one is not working then you can use the below one

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

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;

If you use long codes and want to write less for temporary table create this procedure:

CREATE PROCEDURE MF_DROP (@TEMP AS VARCHAR(100)) AS
    EXEC('IF OBJECT_ID(''TEMPDB.DBO.' + @TEMP + ''', ''U'') IS NOT NULL DROP TABLE ' + @TEMP)

In execution:

EXEC MF_DROP #A
CREATE TABLE #A (I INT) ....

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

https://i.stack.imgur.com/crsin.png

Select and Right Click the Table you wish to delete, then delete. Such a screen should be displayed. Click Update Database to confirm.

https://i.stack.imgur.com/42ASB.png

This method is very safe as it gives you the feedback and will warn of any relations of the deleted table with other tables.