Examine Temporary Table

Lance James 366 Reputation points
2022-04-20T12:53:46.337+00:00

In C# I create a temp table using

string strSQLTempTable = "select top 0 * into #tempTable from dbo." + DataOperations._sqlTable; //CREATE TEMP TABLE  

followed by

SqlCommand sqlCmdTemp = new SqlCommand(strSQLTempTable, DestinationConnection);  

This works fine and everything has been fine for months. However, as of this morning I receive an error regarding Column mapping. I would like to review the schema of the temp table created to verify it matches the schema of the DataTable that is trying to write to it. I can see the temp table in the SQL server and I can run some queries against it but everything is blank. I am doing this while in break mode of the debugger so everything is still live.

SELECT * FROM TempDB.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME IN (  
SELECT NAME FROM TempDB.SYS.TABLES WHERE OBJECT_ID=OBJECT_ID('TempDB.dbo.#tempTable')  
);  
GO  
   
SELECT * FROM TempDB.SYS.COLUMNS WHERE OBJECT_ID=OBJECT_ID('TempDB.dbo.#tempTable');  
GO  
   
SELECT * FROM TempDB.SYS.TABLES WHERE OBJECT_ID=OBJECT_ID('TempDB.dbo.#tempTable');  
GO  

Is this a case where I am trying to reach the temp table out of context and therefore, though it is visible I am prevented for examining the schema?

This command produces an error.

EXEC TempDB..SP_HELP '#tempTable'  

194706-image.png

Regards,
Lance

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,328 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,597 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Naomi 7,366 Reputation points
    2022-04-20T15:00:20.24+00:00

    Can you alternatively just examine the table you used to create the temp table from?


  2. LiHong-MSFT 10,046 Reputation points
    2022-04-21T09:04:41.567+00:00

    Hi @Lance James
    Have you tried this ?

     SELECT * FROM TempDB.SYS.TABLES WHERE  name LIKE N'#tempTable[_]%'  
    

    Since the name of temporary table has a hex code suffix and a bunch of underscores to disambiguate between sessions,so we can't use name ='#tempTable'
    If you want to determine if such an object exists in your session, you could do:

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

    Or when you are using SQL Server 2016+,then you could use this:

    DROP TABLE IF EXISTS #tempTable  
    

    Best regards,
    LiHong

    0 comments No comments