The SQL statement is used to determine whether a table name exists before deletion.

When using SQL, when deleting a database table, we need to determine whether the table exists before deleting it. Next let's take a look at how to use SQL to determine whether a table exists:

The SQL code is as follows:

The code is as follows:	Copy code
If exists (select * from INFORMATION_SCHEMA.tables Where table_name = 'tab ') Drop table tab

It can be seen that the SQL statement is very simple to determine whether a table exists. Deleting the table after the judgment can avoid the error of the table.

Let's do something more advanced.

In sqlserver, check whether the table exists before creating the table. If so, delete the existing table.

 

The code is as follows:	Copy code
---- SQL-Server If exists (select 1 From sysobjects Where id = object_id ('emp ') And type = 'u ') Drop table S_Evaluate Go   -- CREATE Table: EMP Create table EMP ( Id numeric identity, -- Evaluation ID Conclusion text null -- Conclusion Constraint PK_S_EMP primary key nonclustered (Id) )  Go

Whether the temporary table exists

USE [instance name]

The code is as follows:	Copy code
GO If exists (SELECT * FROM dbo. SysObjects where id = object_id (n' [table name] ') and objectproperty (ID, 'istable') = 1) PRINT 'exist' ELSE PRINT 'Nonexistent'

For example:

The code is as follows:	Copy code
Use fireweb; Go If exists (SELECT * FROM dbo. SysObjects where id = object_id (n'temp _ TBL ') and objectproperty (ID, 'istable') = 1) PRINT 'exist' ELSE PRINT 'Nonexistent'

2. Whether a temporary table exists:

Method 1:

The code is as follows:	Copy code
Use fireweb; Go If exists (select * from tempdb .. sysobjects where id = object_id ('tempdb .. # TEMP_TBL ')) PRINT 'exist' ELSE PRINT 'Nonexistent'

Method 2:

The code is as follows:	Copy code
Use fireweb; Go If exists (select * from tempdb. dbo. sysobjects where id = object_id (N 'tempdb .. # TEMP_TBL ') and type = 'u ') PRINT 'exist' ELSE PRINT 'Nonexistent'