The table name is Scores
.
Is it correct to do the following?
IF EXISTS(SELECT *
FROM dbo.Scores)
DROP TABLE dbo.Scores
This question is related to
sql-server
tsql
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.
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.
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).
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
Or:
if exists (select * from sys.objects where name = 'Scores' and type = 'u')
drop table Scores
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
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;
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
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]
I hope this helps:
begin try drop table #tempTable end try
begin catch end catch
There is an easier way
DROP TABLE IF EXISTS table_name;
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"
Source: Stackoverflow.com