Have you ever been writing a bunch of SQL code in SQL Server Management Studio (SSMS), only to have the query editor put a red, squiggly line underneath a table, column, view, procedure or any other object that you know exists? Of course, that red, squiggly line means that IntelliSense is not recognizing that object, thus it will also not appear in your auto-complete pop-ups either. If you too have come across this, it is likely that the object has been newly created or modified since you last opened SSMS.
I have known about this issue for years, but have just ignored it up to now. Luckily for us, there is a simple solution. Though I try not to rely on IntelliSense anyways, I decided to take to Google and see if I could figure out how to fix this problem without restarting SSMS. The answer lies in the cache.
YES, IT DOES EXIST, YOU LITTLE…!
The other day I created a new table in my database. I ran the script, checked in the Object Explorer to make sure it was created properly, then started writing Stored Procedures against it. It became quickly apparent that IntelliSense was not recognizing my table, as illustrated below:
Figure 1: Bad Code Before Refresh
My scripts would still run and execute fine, as the table did actually exist, but IntelliSense can make it easy to double-check yourself when writing code. Recognizing that it was an IntelliSense issue, I searched for solutions.
IntelliSense is Cached
When you launch SSMS and make your first connection, the IntelliSense “database” is cached with that snapshot. In order to recognize new or changed objects in the database (this includes tables, columns, keys, procedures, views, triggers, etc.) you need to refresh the IntelliSense Local Cache. I found this useful article about How to Refresh the IntelliSense Cache in SQL Server Management Studio which shows you the two methods of how to easily refresh the cache. They are as follows:
- With your Query Editor selected, select menu Edit -> IntelliSense -> Refresh Local Cache
- With your Query Editor selected, press CTRL + SHIFT + R
Obviously I prefer the second one as it is really quick if you notice that your objects are not being recognized. So I went ahead and hit:
And suddenly my table was recognized!
Figure 2: Objects Properly Recognized
Does It Have To Be Manual?
The simple answer is yes. While it does seem mildly annoying that I have to manually refresh the IntelliSense cache whenever I make a schema change, it is still a small price to pay in the long run. Someone on StackOverflow already asked the question about automating the IntelliSense refresh process. It appears that there is no automatic way of accomplishing this, so print out the
CTRL + SHIFT + R image above and paste it on your monitor. Hooray for keyboard shortcuts!