I do not fully understand the "USE" statement in Transact-SQL and how it affects the scope of temp tables. I have a user-defined table type in one database but not another, and I've found I need to "USE" that database in order to define a table of that type. Earlier in the query, I define a temporary table. After the "USE" statement, SSMS does not recognize the temp table as a valid object name, however I can still query from it without error.

The skeleton of my SQL query is as follows:

USE MYDATABASE1

[... a bunch of code I did not write...]

SELECT * INTO #TEMP_TABLE FROM #SOME_EARLIER_TEMP_TABLE

USE MYDATABASE2

DECLARE @MYTABLE MyUserDefinedTableType -- this table type only exists in MYDATABASE2
INSERT INTO @MYTABLE(Col1, Col2)
    SELECT Col1, Col2 FROM (SELECT * FROM MYDATABASE2.dbo.SOME_TABLE_VALUED_FUNCTION(param1, param2)) T

SELECT A.*, B.Col2 
    FROM #TEMP_TABLE A 
    CROSS APPLY DATABASE2.dbo.SOME_OTHER_TABLE_VALUED_FUNCTION(@MYTABLE, A.SomeColumn) B

In the last SELECT statement, SSMS has red squiggly lines under "A.*" and "#TEMP_TABLE", however there is no error running the query.

So my question is: am I doing something "wrong" even though my query still works? Assuming the initial "USE MYDATABASE1" is necessary, what is the correct way to switch databases while still having #TEMP_TABLE available as a valid object name? (Note that moving the definition of #TEMP_TABLE to after "USE MYDATABASE2" would just shift the problem to #SOME_EARLIER_TEMP_TABLE.)

CodePudding user response：

In SQL USE basically tells the query which database is the "default" database.

Temp tables can play tricks on intellisense - unless they're explicitly defined using the CREATE TABLE #MyTempTable route, intellisense doesn't really know what to do with them a lot of the time. Don't worry though - temp tables are scoped to the query.

Although I do feel it's worth pointing out: while UDTs are database specific, you can create an assembly to use across databases