Whether the temporary table exists

1. in SQL Server, use select object_id ('tempdb .. # temptable') to determine whether a temporary table has been created. (The returned value is an integer. If no value is set, the return value is null)

Tempdb is a database that stores temporary tables.

Before creating a temporary table, use the following statement to determine whether the temporary table already exists. If the temporary table already exists, delete it.

If not (select object_id ('tempdb .. # temptable') is null drop table # temptable

Eg: If not (select object_id ('tempdb .. #1234 ') is null drop table #1234 easier writing three functions are the same

If exists (select * From tempdb. DBO. sysobjects where id = object_id (N 'tempdb .. #1234 ') Drop table #1234

If exists (select * From tempdb .. sysobjects where id = object_id (N 'tempdb .. #1234 ') execute ('drop table #1234 ')

2. Temporary tables are similar to permanent tables, but temporary tables are stored in tempdb. They are automatically deleted when they are no longer used. Temporary tables can be local or global. They differ in terms of name, visibility, and availability.

The name of the local temporary table starts with a single digit (#). They are only visible to the current user connection and are deleted when the user is disconnected from the SQL server instance.

The name of the global temporary table starts with two numeric symbols (#). After being created, the table is visible to all users. When all users that reference the table are disconnected from SQL Server, the table is deleted.

3. Permanent Table id = object_id (n' [DBO]. [dh_sjmt] ')

If exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. [dh_sjmt] ') and objectproperty (ID, N 'isusertable') = 1) Drop table [DBO]. [dh_sjmt]

4. Select * From DBO. sysobjects to query the Permanent Table name. Name/ID/xtype the xtype = 'U'

Select [name] From tempdb .. sysobjects query the temporary table name. Name/ID/xtype the xtype = 'U'

 

 

5. Case Analysis

 

If exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. [tempkh] ') and objectproperty (ID, n'isusertable') = 1)

Drop table [DBO]. [tempkh]

Go

// Export the built-in footstep statements

Create Table [DBO]. [tempkh] (

[Stypeno] [varchar] (15) Collate chinese_prc_ci_as null,

[Colorid] [varchar] (10) Collate chinese_prc_ci_as null

) On [primary]

Go

// Check whether the function exists if the function is the same

If exists (select * From DBO. sysobjects where objectproperty (object_id (n' [DBO]. [tempkh] '), n' isusertable') = 1) -- 'istable') = 1)

Drop table [DBO]. [tempkh]

Go