SSMS and the IntelliSense Red Squiggle Mess

February 21, 2014 • Mark Dickson

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 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:

CTRL SHIFT R

And suddenly my table was recognized!

Figure 2: Objects Properly 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!

Posted in sql and tagged with DDL, Database, IntelliSense, SQL, SQL Server, SQL Server Management Studio, Schema, T-SQL

Mark Dickson

I am a software engineer with 9 years of experience. My primary focus is on Microsoft systems and technologies, in particular I am often involved in web development with ASP.NET MVC and the .NET 4.5 framework using C#. I also have experience in using Spring MVC using Java. Throughout my adventures in coding I have found many nuances and gotchas about coding, in addition to finding good coding practices along with "less-than-good" practices. I would like to share the knowledge I have gained over the years with others such that you all can benefit from my pain without having to endure it yourself. Enjoy!

comments powered by Disqus