Can we access the SQL Server temporary table created in different database?

Temporary tables are nothing new and we have been using this for long time. There are two types of temporary tables; Local that starts with single pound sign (#) and Global that starts with double pound signs (##). Local Temporary Tables are limited to the connection created and will be discarded automatically when the connection is disconnected. Global Temporary Tables are global to the instance and it can be accessed by the anyone connected the instance. It will be dropped automatically when the last referenced connection is dropped.

Now the question is, when a Local Temporary Table is created, can I access it in another database?

Answer is yes and no. See the code below.

USE Sales;
GO

CREATE TABLE #TempTable
(
 Id int
);
GO

-- This works without any issue
SELECT * FROM #TempTable;

The created table can be access without any issue because access is done in the same database using the same connection. If we try the SELECT in another database with different window (different connection);

USE Sales;
GO

-- This will throw "Invalid object name '#TempTable'." error.
SELECT * FROM #TempTable;

You will see an error as above. However if I try to access the table from the same connection but different database;

--USE Sales;
--GO

--CREATE TABLE #TempTable
--(
-- Id int
--);
--GO

--SELECT * FROM #TempTable;

-- Same first connection but different database
USE AdventureWorks2014;
GO

-- This will work
SELECT * FROM #TempTable;

As you see, it is possible. Remember, Local Temporary Tables are limited to the connection, not to the database created, hence the created table can be accessed within any database as long as the connection is same.