You’re darn close – you need to use two dots in your check:
IF OBJECT_ID('tempdb..#tmpTable1') IS NOT NULL
**
|
use two dots here!
Basically, this is saying: check in the tempDB and I don’t care what schema the table is in
As Joe rightfully said: this is not 100% correct: it doesn’t check in every schema – it will only check in the default owner’s schema – normally dbo. So this would work, too:
IF OBJECT_ID('tempdb.dbo.#tmpTable1') IS NOT NULL
If you happen to create your objects in a schema other than the default owner’s, then you’ll need to explicitly specify the schema you’re referring to. But the temp tables in tempDB are indeed creating in the dbo schema.