Geek asks and answers

Search #temp table in sysobjects / INFORMATION_SCHEMA

I run a statement SELECT INTOlike this, so I can manipulate the data before finally dropping the table.

SELECT colA, colB, colC INTO #preop FROM tblRANDOM

However, when I run the statement, and then, without discarding the newly created table, I run any of the following statements: the table was not found? Even scanning through the object explorer I do not see. Where should I look?

SELECT [name] FROM sysobjects WHERE [name] = N'#preop'
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '#preop'
+7
source share
3 answers

Temp , tempdb. , ; . sys.objects sys.tables, sysobjects ( ) INFORMATION_SCHEMA.

SELECT name FROM tempdb.sys.objects WHERE name LIKE N'#preop[_]%';

, , , , :

IF OBJECT_ID('tempdb.dbo.#preop') IS NOT NULL
BEGIN
  DROP TABLE #preop;
END

, , ... , .

+17

tempdb :

IF  EXISTS (SELECT * FROM tempdb.sys.objects 
             WHERE object_id = OBJECT_ID(N'tempdb.[dbo].[#MyProcedure]')
             AND type in (N'P', N'PC'))
BEGIN 
    print 'dropping [dbo].[#MyProcedure]'
    DROP PROCEDURE [dbo].[#MyProcedure]
END
GO
0

, :

CREATE TABLE #T (PK INT IDENTITY(1,1), APP_KEY INT PRIMARY KEY)

SELECT * FROM tempdb.INFORMATION_SCHEMA.COLUMNS c WHERE c.TABLE_NAME LIKE '#T%'
0

More articles:


All Articles