Shulou(Shulou.com)05/31 Report--

How to find and delete temporary tables in sql server? for this problem, this article introduces the corresponding analysis and solutions in detail, hoping to help more partners who want to solve this problem to find a more simple and easy way.

If exists (select * from tempdb..sysobjects where id=object_id ('tempdb..#temp')) drop table # temp temporary tables can create local and global temporary tables. The local temporary table is visible only in the current session; the global temporary table is visible in all sessions. The name of the local temporary table is preceded by a symbol (# table_name), while the name of the global temporary table is preceded by two symbols (# # table_name). The SQL statement references the temporary table using the name specified for table_name in the CREATE TABLE statement: CREATE TABLE # MyTempTable (cola INT PRIMARY KEY) INSERT INTO # MyTempTable VALUES (1) if the local temporary table is created by a stored procedure or by an application that is executed by multiple users at the same time, SQL Server must be able to distinguish between tables created by different users. To do this, SQL Server internally appends a numeric suffix to the table name of each local temporary table. The full name of a temporary table stored in the sysobjects table of the tempdb database consists of the table name specified in the CREATE TABLE statement and the system-generated numeric suffix. To allow suffixes to be appended, the table name table_name specified for the local temporary table cannot exceed 116characters. Unless you explicitly drop the temporary table using the DROP TABLE statement, the temporary table is automatically dropped by the system when it exits its scope: when the stored procedure completes, the local temporary table created in the stored procedure is automatically removed. All nested stored procedures executed by the stored procedure that created the table can reference this table. However, the process that calls the stored procedure that created this table cannot reference this table. All other local temporary tables are automatically removed at the end of the current session. The global temporary table is automatically removed when the session that created this table ends and other tasks stop referencing it. The association between a task and a table is maintained only during the lifetime of a single Transact-SQL statement. In other words, when the session that creates the global temporary table ends and the last Transact-SQL statement that references the table completes, the table is automatically dropped. The local temporary table created in the stored procedure or trigger is different from the temporary table with the same name created before the stored procedure or trigger is called. If the query references a temporary table and there are two temporary tables with the same name, the query is not defined for which table the query is parsed. A nested stored procedure can also create a temporary table with the same name as the temporary table created by the stored procedure that called it. All references to the table name in a nested stored procedure are interpreted as tables created for that nested procedure. For example, the copy code is as follows: CREATE PROCEDURE Test2 AS CREATE TABLE # t (x INT PRIMARY KEY) INSERT INTO # t VALUES (2) SELECT Test2Col = x FROM # t GO CREATE PROCEDURE Test1 AS CREATE TABLE # t (x INT PRIMARY KEY) INSERT INTO # t VALUES (1) SELECT Test1Col = x FROM # t EXEC Test2 GO CREATE TABLE # t (x INT PRIMARY KEY) INSERT INTO # t VALUES (99) GO EXEC Test1 GO

Here is the result set: (1 row (s) affected) Test1Col-1 (1 row (s) affected) Test2Col-2 when creating local or global temporary tables, the CREATE TABLE syntax supports all constraint definitions except FOREIGN KEY constraints. If you specify a FOREIGN KEY constraint in a temporary table, the statement returns a warning that the constraint has been ignored and that the table is still created, but does not have a FOREIGN KEY constraint. Temporary tables cannot be referenced in FOREIGN KEY constraints. Consider using table variables instead of temporary tables. Temporary tables are useful when you need to explicitly create indexes on temporary tables, or when multiple stored procedures or functions need to use table values. In general, table variables provide more efficient query processing.

This is the answer to the question about how to find and delete temporary tables in sql server. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel for more related knowledge.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.