Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips













































   Got a SQL tip?
            We want to know!

Troubleshooting IntelliSense in SQL Server Management Studio 2012

MSSQLTips author Aaron Bertrand By:   |   Read Comments (27)   |   Related Tips: More > SQL Server Management Studio
Problem

IntelliSense can be a very powerful ally for the T-SQL developer. It can significantly reduce keystrokes, prevent you from guessing at object names, make sure you spell them correctly (and use the right case), and can help you understand the interface to several programmable entities such as stored procedures and built-in functions. In Management Studio 2012, there are some enhancements to IntelliSense that will make it even more powerful - including more intelligent caching, and partial keyword matching (so you no longer have to remember what letter the waits DMV *starts* with - just type "waits" and it will narrow it down).

I have seen several cases in forums, newsgroups and on twitter where folks complain that IntelliSense is "not working." Now, "not working" can cover a wide variety of things. For the purpose of this tip, we're going to restrict that to not behaving as designed, as opposed to not behaving as desired (for example, if you don't like that pressing the space bar or tab auto-completes for you, that's a different discussion). I thought it would be useful to list out the common symptoms and how you can try to resolve them. Note that most of these items are also applicable to previous versions.

Solution

There are a couple of different symptoms that can lead you to believe that IntelliSense is "not working." One is where the IntelliSense features (auto-complete / list members / tool-tips / underlining invalid references) do not function at all. Another is where the completion list appears, but it does not contain the object(s) you're trying to use.

  • IntelliSense does not function at all

    There are several potential reasons why IntelliSense may not be working at all; some are more obvious than others.

    • Make sure that IntelliSense is enabled for Management Studio under Tools > Options > Text Editor > Transact-SQL > IntelliSense:

      Tools / Options / Text Editor / Transact-SQL / IntelliSense

    • Make sure that IntelliSense is enabled for the current query window by checking the Query > IntelliSense Enabled menu option (it should be enabled):

      Query / IntelliSense Enabled

      ...or by checking that the toolbar icon is enabled:

      Toolbar : IntelliSense Enabled

    • Since IntelliSense does not work in SQLCMD mode, ensure that you haven't enabled this by checking the Query > SQLCMD Mode menu option (it should NOT be enabled):

      Query / SQLCMD Mode

    • Check that your script isn't too large. To prevent performance issues, SSMS will not try to parse a script that is larger than the size set in Tools > Options > Text Editor > Transact-SQL > IntelliSense > Maximum script size. You can adjust this setting if you are working with very large files, or consider breaking them up into smaller scripts:

      Tools / Options / Text Editor / Transact-SQL / IntelliSense / Maximum script size

    • Check that your cursor is in a position where IntelliSense is actually supported. It may not be for a couple of reasons:

      • There are certain parts of T-SQL grammar that IntelliSense doesn't yet understand. In Books Online they document the syntax that is supported; unfortunately, there isn't an authoritative list of syntax that is not supported. One example is when creating a check constraint; copy the following code, put your cursor between SELECT and FROM, and try to list members using Ctrl+J:

        CREATE TABLE dbo.foo
        (
         i INT CHECK (i IN (SELECT <put cursor here> FROM sys.objects))
        );

        If you copy the SELECT query alone to another query window and try the same experiment, the completion list will appear (unless you are affected by one of the other issues in this tip).

      • Depending on your version of SSMS, IntelliSense may not be able to parse correctly because there are errors further up in the batch. Where feasible, you can add a GO before the current line to see if that temporarily resolves the issue, otherwise you will need to go up and fix the other errors. I haven't noticed this to be an issue in SQL Server 2012 - as long as previous statements are terminated (or the beginning of the current statement is obvious and unambiguous), most IntelliSense functions don't care if there are errors outside of the current statement.
         
      • Your cursor may be inside a comment or a string literal, where IntelliSense parsing simply doesn't dare to venture.

    • Verify that the connection to your server is active. You may have been disconnected, but haven't noticed because you are not actively running queries. You can try to re-establish the connection by running a query as simple as:

      SELECT 1;

      At first you may see an error message about your connection being disconnected or forcibly closed, depending on your version of Management Studio. You just need to try one more time to remind SSMS that, even though the server was temporarily disconnected, it is still there.

    • Verify that the destination server is running an appropriate version of SQL Server; IntelliSense does not function against SQL Server 2005 or previous versions. (You can read a lot of background on this in Connect Item #341872.) You can check the version of the destination server using the following query in the same window:

      SELECT SERVERPROPERTY('ProductVersion');

      If this query yields a build number starting with "8." or "9.", then IntelliSense is not working because the target version is not supported.

    • Verify that the destination server is not running SQL Azure which, like SQL Server 2005, is not currently supported by IntelliSense. You can check by running the following query:

      SELECT SERVERPROPERTY('Edition');

      If the result is "SQL Azure" then that is why IntelliSense is not working.

    • This may sound silly, but confirm that you are in fact using Management Studio 2008 or greater. I have seen a couple of people complain that IntelliSense is not working, only to discover that they were in fact running SSMS 2005. You can verify the version in Help > About.

    • Make sure that your client tools are patched to a version at least as high as the server you're trying to connect to. Ideally, your client tools should always be greater than or equal to the highest version you need to manage. If you are also running Visual Studio 2010 on your machine, there were some issues early on that broke IntelliSense, so make sure that Visual Studio is patched to Service Pack 1 and that client tools are updated to the latest service pack for the version of SQL Server you are using.

    • For SQL Server 2012, there is a bug with Contained Databases, where IntelliSense does not function completely for a "contained" database user (see Connect Item #717063). Some of the IntelliSense features will function fine (for example, syntax error highlighting), but none of the completion lists, invalid object/column highlighting, or tooltip helpers will work. As of the time of writing, I haven't received official confirmation from Microsoft about this bug, never mind when it will be fixed, so it is something to keep in mind if you are using the Contained Databases feature. If this affects you, hopefully you can switch to a server-level login until the issue is resolved.

    • If you are using 3rd-party add-ins, try disabling or uninstalling them in the odd event that they are interfering with IntelliSense. This is especially true for plug-ins that are part of a beta or preview release and/or interact with the text editor directly.

    • And finally, you may just need to be patient. Check your network speed, the general health of the server, and be understanding if your metadata is quite large - if you are running SAP, for example, there's a lot more data to bring across than AdventureWorks. When SSMS is attempting to get metadata from the destination server, it may time out before it starts receiving results. This timeout is hard-coded to two seconds in SSMS 2012, but in previous versions, it would wait for the database connection to time out (which could be 30 seconds or more). It will continue trying in the background, so you may not see the drop-down appear right away, but it could appear after successive attempts at pressing Ctrl+J. You can force it to try to reload by pressing Ctrl+Shift+R or the menu option Edit > IntelliSense > Refresh Local Cache. Once metadata is retrieved in the background, the data is cached in local memory. If the connection is working and none of the above factors are in play, the cache will eventually be populated and you should be able to use all of the functionality. But again, on SSMS 2008 or SSMS 2008 R2, this may take a little bit longer because of the more relaxed timeout.

  • Some objects or columns don't show up, or are incorrectly marked as invalid

    There are a number of reasons why the completion list may not actually be complete or that an object or column name is underlined and marked as incorrect. One is quite common, but several others are possible too.

    • By far the most common scenario is that your local, in-memory cache is stale. This can be more prevalent if you switch databases often using the USE command, or if the database is changing in other query windows or by other users. You can refresh the cache by pressing Ctrl+Shift+R or by selecting the menu option Edit > IntelliSense > Refresh Local Cache. As above, you may need to wait for the cache to fully load, depending on the size of your metadata and other resources involved.

    • (Updated October 2014.) The object you're trying to use might be a synonym. Synonym support was not added until SQL Server 2012 (see Connect Item #331633) but it does not appear to work for everyone (see Connect Item #744975 (link: http://connect.microsoft.com/SQLServer/feedback/details/744975/ssms-intellisense-does-not-recognize-synonyms-still-broken)), and they don't have any plans to fix that. It also is not expected to ever be back-ported to SQL Server 2008 or SQL Server 2008 R2, especially since those releases are now officially out of mainstream support.

    • There are some elements in the sys schema that do not show up even though they exist. My assumption here is that some subset of system objects must be checked against some list outside of a simple metadata retrieval. Based on a bug (see Connect Item #621445) filed by Jonathan Kehayias (@SQLPoolBoy), some of the new extended events DMVs were moved from the dbo schema to the sys schema. One example is sys.trace_xe_event_map. However, if you try to get IntelliSense to auto-complete this DMV, it does not show up:

      Completion list for sys objects containing 'trace_'

      These don't show up under the dbo schema in auto-complete either, in case you were wondering.

    • The object you're trying to access might actually be in a different database. Hopefully this isn't something that trips you up, but I wanted to list it for completeness.

    • You may have been denied permissions on the object or it may reside in a schema to which you don't have access. To test this, try accessing it as a more privileged login.

    • And like the last point in the previous section, you may just be experiencing performance due to network or other resources, or sheer metadata size. For a large enough schema, the cache will get populated in a "lazy" way - so for example if you are dealing with 20,000 tables and you've only referenced 50 of them so far, those 50 should have detailed information. The other 19,950 may already be represented in the cache, but they may just have basic details such as table name - until they are fully loaded, the list members functionality may be missing column names and other details.

Conclusion

SQL Server 2012 Books Online outlines a variety of other potential issues in the topic Troubleshooting IntelliSense (SQL Server Management Studio). There are topics for other versions of SQL Server as well: SQL Server 2008 | SQL Server 2008 R2. There's even a SQL Server 2005 topic, presumably added to Books Online at a time when they intended to include IntelliSense in that version of Management Studio (which they never did, of course).

I hope that gives you some avenues to try if you are finding that IntelliSense isn't working (partially or at all). And if you've shied away from IntelliSense in SSMS 2008 or SSMS 2008 R2, I hope you'll give it another shake in SSMS 2012.

Next Steps


Last Update: 2/1/2012


About the author
MSSQLTips author Aaron Bertrand
Aaron Bertrand is a Senior Consultant at SQL Sentry, Inc., and has been contributing to the community for over a decade, first earning the Microsoft MVP award in 1997.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Thursday, October 30, 2014 - 4:06:21 PM - Excellent!!! Read The Tip

Great!!! I had SQLCMD Enabled!!


Thanks for your help!


Wednesday, October 08, 2014 - 2:01:57 PM - Chris Bartolone Read The Tip

That's a bummer! Thanks for the info. Will you update your article per this comment?


Wednesday, October 08, 2014 - 11:23:18 AM - Aaron Bertrand Read The Tip

Chris, 

I don't have any good news for you, sorry. See the most recent comment by Microsoft here:

http://connect.microsoft.com/SQLServer/feedback/details/744975/ssms-intellisense-does-not-recognize-synonyms-still-broken


Wednesday, October 08, 2014 - 10:58:48 AM - Chris Bartolone Read The Tip

I am having issues with intellisense for synonyms in SQL Server 2012. Is anyone else having issues? Do you know a work around?


Thursday, September 18, 2014 - 10:39:46 PM - Amandeep Singh Read The Tip

Thanks a load dear, was not working because SQL CMD mode was enabled :(


Wednesday, June 25, 2014 - 1:44:44 PM - David Read The Tip

Ctrl-Shift-R seems to restore intellisense back to sanity, thanks! This was driving me crazy, I have used intellisense since Borland's Delphi last century and never had the problems/issues w/ it which I've had w/ SQL server's manyKludge studio.

 


Monday, May 12, 2014 - 3:04:22 PM - Janya Lee Erwin Read The Tip

Correction to the prior post: Intellisense IS WORKING after SQL2012 sp1 cu9, but may take a full minute for CTRL J to load the cache for the current database context when switching databases, and more when connecting to different SQL servers. SQL 2012 "appears" to be immediately aware of newly created tables, but takes a long time to load the cache for the current connection; whereas SQL 2008 "seemed" to load intellisense more quickly, but not display new objects. This applies to a basic intellisense test that selects from table name.

CONCLUSION: the cu is worth loading, in addition to settings recommended above by Aaron Bertrand. For those of us that work quickly and changing connections constantly, it is a productivity constraint not anticipated in "upgrading from SQL 2008 to SQL 2012", so hopefully Microsoft will acknowledge and address if enough of us document the problem.


Monday, May 12, 2014 - 2:07:39 PM - Janya Lee Erwin Read The Tip

Microsoft Intel 64-bit SQL 2012 SP1 CU9 did NOT resolve the issue as this kb indicated the CUs should: http://support.microsoft.com/kb/2897539.  Nor did disabling, reenabling intellisense and restarting SSMS.  Still looking for an adequate Microsoft recommended solution to drag and drop or a whole lot of typing

 

 


Friday, May 02, 2014 - 12:33:20 AM - Vijay Sahu Read The Tip

I already tried this steps and still no solution i found for intelligence..
Will let me know if you have any other way to resolve this Code Intelligence problem.


Thursday, April 24, 2014 - 12:53:45 PM - Aaron Bertrand Read The Tip

FYI there is a fix for 2012 SP1 and 2014 where IntelliSense "does not work properly" (according to the KB):

http://support.microsoft.com/kb/2897539


Thursday, April 24, 2014 - 12:53:06 PM - Aaron Bertrand Read The Tip

Rachel, I am not sure I can help with troubleshooting monitor issues.


Tuesday, April 15, 2014 - 12:38:30 PM - Rachel Wilson Read The Tip

My problem with SSMS intellisense is that it won't work on my second monitor, and this is what's causing the issue.  Works fine on the main monitor.

I prefer having SSMS up on the second monitor because it's bigger and easier on my already tired and aching eyes.

I has SSMS 2012 and I've seen patches for 2008 and 2010.  Are there any for 2012 to stop this aggravating issue.  

My computer has several servers and each can hold 10, 15, 20, or even more db instances which each in turn have about 40, 50 or more tables.  So having to go back and scroll through to find my db and then the table and then hit the columns is quite annoying.  And I can't memorize that many columns and which tables there are in.

Help?


Thursday, March 13, 2014 - 4:08:22 PM - Richard Zorge Read The Tip

Thank you very much in your very useful posts.  In my case, I run SQL Server Managment Studio ve.11.0.2100.60 on Windows 7 Enterprise SP1 32-bit.
In my case  under Tools | Options | Text Editor | Transact-SQL | Enable IntelliSense is checked.  Yet under Query menu IntelliSense Enabled is greyed out.  At the same time SQLCMD Mode is not enabled.  Enabling or disabling it does not affect the status of IntelliSense Enabled menu option. 
I would love to know why IntelliSense is disabled and what I could do to enable it.

Thanks again for your great work. 


Saturday, November 23, 2013 - 8:51:41 AM - Greg Robidoux Read The Tip

Mark, take a look at this tip to see if this helps: http://www.mssqltips.com/sqlservertip/2291/enabling-intellisense-and-refreshing-intellisense-data-in-ssms-2008/


Friday, November 22, 2013 - 6:08:57 PM - Aaron Bertrand Read The Tip

Sorry Mark, I haven't seen that specific scenario.


Friday, November 22, 2013 - 5:29:51 PM - Mark Freeman Read The Tip

I have SSMS 2012 and have Intellisense enabled in Tools | Options | Text Editor | Transact-SQL | IntelliSense. I open a new query window. Query | Intellisense is greyed out in the menu. When I enter Ctrl-Q Ctrl-I, the status bar displays "The key combination (Ctrl+Q, Ctrl+I) is bound to command (IntelliSense Enabled) which is not currently available. What could cause this?

 


Thursday, September 05, 2013 - 4:19:09 PM - Amy Read The Tip

What gets me is that after you've accepted the suggestion, if you continue typing, your cursor will jump back to somewhere in the middle of what you accepted and will insert some random intellisense suggestion right there. So I spend as much time deleting this random crap as I might save using intellisense. Is there any way to control that?


Friday, May 31, 2013 - 10:23:41 AM - Andrew Read The Tip

Thanks for sharing this info. Unfortinately it hasn't helped me. I'm using SSMS 2012 Enterprise Edition and despite performing all the tasks and checks you've mentioned in this article, intellisense still does not work for me. I've even tried some of the suggestions in the comments as well as suggestions from other articles. If I type something as simple as "select * from sys." I would expect the sys object list to popup, alas it does not. This is extremely frustrating and pretty much makes me want to use some other tool that actually works. I've never had a problem with intellisense in Toad for example. This is something that you'd think wouldn't require so much attnetion, but apparently MS ephed it up somehow. It's comepletely useless.


Thursday, November 29, 2012 - 1:21:55 PM - Marlon Jimenez Read The Tip

I fulfill the given check list, however Intellisense never worked. The problem was for a specific database.

Solution: Disassociate the user from that database and then associate it again!!!!

Hope it helps.


Sunday, November 18, 2012 - 12:07:01 PM - Aaron Bertrand Read The Tip

Robert and Dmitry, I don't know how to control this behavior, but on my machine spacebar does not auto-accept partial words - I have to manually scroll to the selected word in order for spacebar to complete the word.


Sunday, November 18, 2012 - 7:28:18 AM - Dmitry Read The Tip

Same question, as Robert's. I am really annoyed that when I hit spacebar, Intellisense chooses to enter some word from the list. I just want to enter spacebar.

In Visual Studio Intellisense can be in 2 modes: suggesting mode, completion mode. Suggesting mode makes Intellisense paste the highlighted word only by pressing enter or tab, spacebar only enters space as expected. Is there something similar in SQL Server Management Studio?
Please help. 


Tuesday, November 13, 2012 - 5:42:16 PM - Robert Read The Tip

Is there a way to control the 'accept' keys for intellisense? I am really annoyed that the spacebar accepts a partial word when only tab or enter should be the only ones.


Sunday, October 21, 2012 - 7:29:20 PM - David Basri Read The Tip

You are certainly correct that no software can please everyone. Ever.

This seems to be a question of the intended use. You are looking for items among the plethora of system objects. I tend to just be looking to complete standard column, table or view objects. You are trying to locate objects and I am trying to speed up typing queries.

This was annoying enough for me to comment on because the 2008 version worked so well for what I needed it to do. I will get used to Ctrl-Enter soon enough.

 


Sunday, October 21, 2012 - 12:56:55 PM - Aaron Bertrand Read The Tip

David, I don't think they could create a version of IntelliSense that satisfies everyone. Personally I like the new version better for this reason: when I am looking for a DMV involving, say, transactions, I don't have to know whether it starts with dm_db_ or dm_tran_ or dm_os_ or what have you. I san type:

sys.tran

And the auto-complete list is populated with items that *contain* "tran" - unlike in 2008, where I would already have eliminated the list to objects that *start* with "tran." For me that's a big win and, since I found IntelliSense hardly usable at all in 2008 (in fact I almost always turned it off completely), I'm still way ahead in terms of productivity, extra keystroke or not.

Like many things, YMMV.


Saturday, October 20, 2012 - 11:37:31 AM - David Basri Read The Tip

The big issue in my opinion is that SMSS 2012 Intellisense is simply less efficient than SMSS 2008 Intellisense.

For SQL 2012 an additional keystroke is required for almost every object. In SMSS 2008 when the list was narrowed to one object you could just press enter to insert it. In SMSS 2012 you have to hit a down arrow or click on the object to select it. I did discover that pressing Ctrl-Enter selects the "suggested" object, but that is still an additional keystroke.


Friday, September 28, 2012 - 12:34:44 PM - anon Read The Tip

Intellisense wasn't working for me after installing even though the settings you mentioned were all correct.  Disabling Intellisense and then re-enabling it fixed the problem for me.

 

 

 


Thursday, August 02, 2012 - 8:05:09 AM - Thomas Pullen Read The Tip

I have just discovered that blocking during a database restore (on 2012 rtm) can cause Intellisense to stop working too. It appears whatever SSMS does in the background to populate its cache can't coexist with the db restore.

I have created a connect item about this:-

https://connect.microsoft.com/SQLServer/feedback/details/756597/database-restore-causes-blocking-and-breaks-managament-studio-intellisense




 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.