As title states. I have seen threads where folks talk about this and various fixes for 2012 for example (one involved a patch download), but nothing on 2017 - most all of them talk about Intellisense not working at all and the various fixes.
It happens constantly for me that Intellisense works for some tables and not for others. In this particular instance, where some are picked up by Intellisense and some not, all the tables I am talking about just happen to be temp tables, though I'm sure that has nothing to do with the issue
Note that (since temp tables) all these are obviously part of the same SSMS instance and in the same query window..
So, a couple of things. CTRL+SHIFT+R when I try this, apparently gets rid of all intellisense. Loading the intellisense cache seems to get rid of what was in cache!?!?
Another thing, I had an intuition about when this issue of 'losing' temp tables was happening. It appears that if you create a temp table and then change the name of the temp table viz a viz the code that its creating the table for with attendant table exists checks, e.g.
IF OBJECT_ID('tempdb..#OUTPUT') IS NOT NULL DROP TABLE #OUTPUT
SELECT PE.PAT_ID
INTO #OUTPUT
FROM TBL
After the above runs, you will get #OUTPUT in Intellisense, however, if you change the name of the temp table to, say #OUTPUT1, then immediately, without dropping the original #OUTPUT table and without even running #OUTPUT1, intellisense will stop working for #OUTPUT. Its like SSMS looks for #OUTPUT anywhere in the sql code, in the code window and if it doesn't find it, assumes its not there?
Kinda annoying..Does anyone know a way to stop this?
Not sure your scenario could ever be fixed - or will be...
The problem is that temp tables are scoped to a connection and only visible to that connection. Once you create a temp table - and reference that temp table in your code - intellisense can be used to identify the columns available in that temp table.
When you change the reference in your code - intellisense is going to look for that new temp table, and since that new temp table doesn't exist nothing can be returned. You are expecting that intellisense knows about a temp table that isn't created in the code...or that intellisense scans for all available temp tables for that connection - which isn't possible.
Ah..ya, i thought that intellisense would scan for any temp tables created by me (my user id) per my current connection, per the currently open query window..
It sounds like you are saying that it would be too intensive for sql server to look for my temp tables per the description i just wrote? Yet, given restrictions of current query window, current connection & my user id, it doesn't seem like that would use so much memory(?)..Does it have to scan through too many objects? It can't restrict its search to only temp tables (and my login/current query window/connection)? Or am i stating this issue incorrectly?
I don't think it is an issue about being too intensive...rather it is an issue around what triggers intellisense to cache the object.
For normal tables/views/functions - these are cached when the window is opened and only refreshed when you manually refresh the cache (or certain triggering actions). For temp tables - the trigger is the code reference in either a CREATE TABLE or SELECT INTO statement...
When you change the create/select to reference a different object - the temp table cache is refreshed and updated based on the referenced objects in the code.
ok, so its inherent in the design. So, i guess i wonder why the design wasn't to create a separate cache that, when the temp tables are created, contains the tables and (perhaps) another cache for the columns, other objects, etc..did they not design it that way because THAT would too intensive? Or perhaps another reason? Or several reasons..?
btw, this is just simple curiosity at this point..
It seems to be inherent in the design - and other intellisense products appear to do the same thing. I don't use the built-in intellisense - I have a third-party tool that works the same way.
