SQL Server Articles, SQL Server Tips, SQL Server Tutorials, SQL Server Tuning, SQL Server DBA, SQL Server Basics, Training, etc - MyTechMantra.com

How to Enable and Refresh IntelliSense in SQL Server Management Studio (SSMS)


Introduction

In SQL Server 2008, Microsoft had introduced IntelliSense feature in SQL Server Management Studio (SSMS). As a Developer or as a DBA you may face issues with IntelliSense feature not to be working or not displaying latest scheme changes when you are writing TSQL code within the Query Window of SQL Server Management Studio (SSMS). In this tip, we will take a look at the steps you can follow to quickly enable and refresh IntelliSense in SSMS to resolve this issue of IntelliSense not displaying the latest schema changes.

What is IntelliSense feature and how it can help?

IntelliSense feature reads internal metadata and lists all of the available objects and its properties, thereby helping Database Developers, DBAs effectively and quickly write TSQL scripts.

However, there are certain occasions when the IntelliSense feature in SSMS doesn’t work. During such scenarios, one can follow the below-mentioned steps to refresh the IntelliSense feature of SQL Server Management Studio (SSMS). The fix mentioned in this article is applicable for SSMS 2008 and higher versions.

The steps mentioned in this article are applicable across SQL Server 2008 Management Studio, SQL Server 2008 R2 Management Studio, SQL Server 2012 Management Studio, SQL Server 2014 Management Studio, and SQL Server 2016 Management Studio.



How to Enable IntelliSense Feature in SSMS

  1. Open SSMS, click Tools -> Options -> Expand Text Editor -> Expand Transact-SQL and click on IntelliSense as shown in the snippet below. Under Transact-SQL IntelliSense Settings ensure “Enable IntelliSense” checkbox is enabled.
How to Enable IntelliSense Feature in SQL Server Management Studio (SSMS)

Related SQL Server Articles…

  • SQL Server Management Studio Resources: Configuration Tips, Articles and FAQs for DBAs & Devs
  • Database Corruption: How to Repair Suspect Database in SQL Server
  • DBA Tutorial: SQL Server Database Backup Tutorial for DBAs and Developers
  • How to Enable BACKUP CHECKSUM Feature in SQL Serverfor all Database Using Trace Flag
  • When Last Time DBCC CHECKDB ran successfully on all the database in an SQL Server Instance
  • Why SQL Server Log backups fails and Full backup succeeds? Learn how to Fix “BACKUP detected corruption in the database log error in SQL Server”

How to Verify Whether IntelliSense Feature is enabled in SQL Server Management Studio (SSMS )

One can verify whether IntelliSense feature is enabled in SSMS click on Query menu and see whether “IntelliSense Enabled” as highlighted in the snippet below.

How to Verify Whether IntelliSense Feature is enabled in SSMS or Not?



How to Refresh IntelliSense feature to view Latest Schema Changes in SSMS

If you are unable to see the latest scheme changes using IntelliSense feature, then you can refresh it using the following method. This method will help you Troubleshoot IntelliSense in SQL Server Management Studio.

Open New Query Window -> Go to Edit -> Expand IntelliSense -> Click Refresh Local Cache or press the shortcut key (CTRL + SHIFT + R) to refresh the local cache as shown in the snippet below.

How to Refresh Local Cache to Fix IntelliSense in SQL Server Management Studio (SSMS)

Some conditions which may affect IntelliSense Behavior are:

  • If there is incomplete statement or coding errors within a cursor code which is just above the location of the insertion point. Then in such cases IntelliSense will not be able to parse the code element and it will stop working. To enable IntelliSense you need to comment the error code.
  • IntelliSense options are not available when the insertion point is inside the quotation marks for example in statements such as WHERE Name LIKE ‘MyTechMantr%’
  • IntelliSense will also stop working when automatic options are turned off. For more information read “How to: Modify IntelliSense Options

References

Ashish Mehta

Ashish Kumar Mehta is a database manager, trainer and technical author. He has more than a decade of IT experience in database administration, performance tuning, database development and technical training on Microsoft SQL Server from SQL Server 2000 to SQL Server 2014. Ashish has authored more than 325 technical articles on SQL Server across leading SQL Server technology portals. Over the last few years, he has also developed and delivered many successful projects in database infrastructure; data warehouse and business intelligence; database migration; and upgrade projects for companies such as Hewlett-Packard, Microsoft, Cognizant and Centrica PLC, UK. He holds an engineering degree in computer science and industry standard certifications from Microsoft including MCITP Database Administrator 2005/2008, MCDBA SQL Server 2000 and MCTS .NET Framework 2.0 Web Applications.

2 comments

  • Ashish,

    Thanks for your help in informing us about Intellisense on SQL Server Management Studio. The article was of great help to our team.

    Your Remote DBA Services are Excellent, and we are delighted to have you as part of our team.

    Thanks
    Prasetyo

Newsletter Signup! Join 15,000+ Professionals




Be Social! Like & Follow Us

Follow us

Don't be shy, get in touch. We love meeting interesting people and making new friends.

Advertisement