SQL Prompt not forgetting outdated learned information

MikeyC

In SQL Prompt 3.8.0.244 if it learns about a table via code, for example:

SELECT *
INTO New_Table
FROM Orig_Table

At that point SQL Prompt adds New_Table to it's cache, and can give you candidates for it.

Once you run that code, and make structure changes to New_Table, and tell SQL Prompt to refresh the cache, it still has the original structure. (In fact if you hover over it the table name in your code it shows the original SELECT INTO as the definition, even if you have since deleted the code from the query.)

It looks to me like the cache refresh, doesn't replace the learned information with the new actual information once the table exists.

Deleting the cache for the DB and re-creating it didn't even resolve the problem for me. (Hopefully deleting the cache and exiting SMSS/SQL Prompt will, but I can't do that right now.)

tanya

Hi,

SQL Prompt caches the scripted objects information from the query window and that is how it shows the new table in the candidate list.
But unfortunately SQL Prompt fails to update the changes done on the object when the query used to create the object remains in the query window.
However if you remove the main query used to create the object from the query window and refresh the cache, you should get the updated information in the schema panel or when you hover over the object.

I hope that helps.

Thanks,
Tanya

MikeyC

So even though the SELECT INTO code is gone, that query window remembers the structure of the SELECT INTO for the table over the real structure of the table?

I would expect the learned table info to get removed from memory when the SELECT INTO statement is deleted from the query.

I have also ran into cases where a query window has old cached information, even though the cache was updated and is correct in another query window. Is every windows cache separate for the same database?

Michael

tanya

Once you remove the query used to create the object and refresh the cache, SQL Prompt should show the updated object information. I did test that on the latest v3.8 build and it does work.
So in your case, once the SELECT INTO code is removed from the query window and the cache is refreshed New_Table, should display the altered structure of the table in the schema panel and in the object definition hints while you hover over the table.

Let me know if that works?

Thanks,
Tanya

MikeyC

No, that isn't how it works for me. (In 3.8.0.244)

Say you type this in a new query window:

SELECT *
INTO JunkSPT
FROM MASTER.dbo.spt_monitor

then you delete it, then you refresh the cache and type:

SELECT * FROM <cursor>

at that point it still suggests JunkSPT with the little red text next to it.

If the table actually exists in the DB it will be listed twice in the candidates once with the red text next to it (the INTO version) and once for the actual table.