How to determine whether a temporary table exists in a stored procedure

A temporary table is a table whose names start with a pound sign. If the temporary table is not removed when the user disconnects, SQL Server automatically removes the temporary table. Temporary tables are not stored in the current database, but stored in the system database tempdb.

There are two types of temporary tables:

Local temporary table

Names of tables starting with. These tables can be seen only when the connection to the local temporary table is created. When the link is disconnected, the temporary table is deleted (the local temporary table is exclusive to the session that creates this link) in this case, the local temporary table is created by the current user and can be accessed only by the Session of the current user..

If a local temporary table is created by a stored procedure or is executed by multiple users at the same timeProgramWhen creating a table (which can be viewed as a different link or Session), the database engine must be able to differentiate the tables created by different users. Therefore, the database engine adds a digital suffix to the table name of each local temporary table internally. Stored inTempdbOfSysobjectsThe full name of a temporary table in a table is composed of the table name specified in the create table statement and the digital suffix generated by the system. To allow append suffixes, specifyTable_nameIt cannot exceed 116 characters.

For a local temporary table, you must note that the local temporary table can be deleted in different situations. Assume that the database creates a local temporary table when executing a stored procedure. In this case, the local temporary table is not automatically deleted when the session ends, but will be deleted after the stored procedure is executed. What does this mean? That is to say, a user initiates a session to execute a special job (for example, the user calls a stored procedure ). In this case, it is the process of creating a sub-session. In this case, note that the local temporary table created by the sub-session is only valid within the sub-session. When this sub-session is terminated (the stored procedure is executed), the temporary table is automatically deleted. That is, for sessions that call this sub-session, the temporary table created for this sub-session is invalid because the temporary table has been automatically deleted when the sub-session is closed. Make an image metaphor. That is to say, the father is now asking his son to build a house. The House will also disappear when the son dies. In this case, the database administrator must pay attention to it. A parent session can only reference data transmitted by a child Session from a temporary table. That is to say, the parent session has only one method to access the data of the temporary table created by the Child session. That is, let the sub-session query or operate on the data in the temporary table, and then return the structure to the parent session. A parent session cannot directly access the temporary table created by a sub-session. Of course, this restriction applies to local temporary tables. For global temporary tables, all users can access them.

Global temporary table

The names of the tables starting with two pound signs. The global temporary table can be seen on all connections, or as long as the global temporary table exists, the user creates a session and is visible to all users. If these tables are not explicitly removed before the connection to create a global temporary table is disconnected, the tables are removed as long as all other tasks stop referencing them. When the connection for creating a global temporary table is disconnected, new tasks cannot reference them any more (in other words, how can old tasks reference them ). After the current statement is executed, the association between the task and the table is removed. Therefore, the global temporary table is removed as long as the connection to the global temporary table is disconnected.

 

How can I determine whether a temporary table already exists?

I have searched many methods on the Internet. I may not be very correct in my simple explanations based on my own usage. please correct me if there are any errors:

Method 1:

If object_id ('tempdb .. # temporary table name') is not null
Begin
Drop table # newtable
End
Go

Method 1 is completely feasible, and there is no problem

 

Method 2:

If exists (select * From sysobjects where objectproperty (object_id ('# temporary table name'), 'istable') = 1)

Begin
Drop table # newtable
End
Go

Method 2 can run. No error is reported, but the temporary table cannot be deleted.

Method 3:

If exists (select 1 from tempdb. DBO. sysobjects where id = object_id ('# temporary table name '))
Begin
Print ('yes ')
Drop table # T
End
Go

Method 3 can be run without errors, but temporary tables cannot be deleted

 

Later, I found that the temporary table name is not saved in sysobjects. The specific reason is unclear. The temporary table can be deleted only when the temporary table name is similar to method 1.

 

Note:

If exists (select * From sysobjects where name = 'Pro _ data_opticalinfo 'and type = 'P ')
Drop procedure ztam_data_opticalinfo
Go

-- In this way, you can determine whether a stored procedure exists. If so, delete the stored procedure.