Why is IntelliSense not working?
July 1st, 200929
Why is IntelliSense not working?
July 1st, 200929
 
 

I see people complain that IntelliSense (new in SQL Server 2008's Management Studio) is not working.  Most of the time, it is for one of two reasons:

  1. The object is not in the local IntelliSense cache, since it was created recently.  You can fix this easily by going to Edit > IntelliSense > Refresh Local Cache, or CTRL+SHIFT+R.
     
  2. The query window is connected to a downlevel server (e.g. SQL Server 2005), where IntelliSense does not function (see Connect #341872 from Whitney Weaver for more info and LOTS of community feedback on this decision :-)).

Little did I know, there are a number of other situations where IntelliSense will not function correctly (e.g. when you have switched to SQLCMD mode).  I was also unaware, until today, that Alan Brewer at Microsoft created a Books Online page that documents these and other cases:

When IntelliSense Is Unavailable

So many thanks for this document Alan, as I think it will help others as well.  What I'm still trying to figure out, though, is why there is a SQL Server 2005 version of the page, since IntelliSense does not exist in Management Studio for 2005, nor does it work in 2008 against 2005 instances.

By: Aaron Bertrand

I am a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. I am a long-time Microsoft MVP, write at Simple Talk, SQLPerformance, and MSSQLTips, and have had the honor of speaking at more conferences than I can remember. In non-tech life, I am a husband, a father of two, a huge hockey and football fan, and my pronouns are he/him.

29 Responses

  1. Rohit says:

    Very nice article

  2. Jim says:

    Appreciate your help but this is soooooooooo frustrating.  Can't microsoft provide a script (powershell, transact sql, or executable) that could diagnose this problem and tell what needs to be done to get intellisense working??????????  They are F*()'ing microsoft and wrote all this cr*((p.

  3. Eric Halverson says:

    Ctrl Shift R   Nice!!

  4. AFomchenko says:

    I have got troubles with this request generated by Intellisense in TempDB.
    We generate and drop many #tables all the time and this request cannot be completed and always been blocked. It looks like it comes from ObjectExplorer somehow.
    Let me know if someone have any idea how to suppress this behavior except of disabling this option. We do a lot of development and constantly switching between prod and test boxes.
    SELECT tr.name AS [Name], tr.object_id AS [ID], CASE WHEN tr.type = N'TR' THEN 1 WHEN tr.type = N'TA' THEN 2 ELSE 1 END AS [ImplementationType],
    CAST(                  tr.is_ms_shipped              AS bit) AS [IsSystemObject],
    CAST(CASE WHEN ISNULL(smtr.definition, ssmtr.definition) IS NULL THEN 1 ELSE 0 END AS bit) AS [IsEncrypted]
    FROM sys.triggers AS tr LEFT OUTER JOIN sys.sql_modules AS smtr ON smtr.object_id = tr.object_id
    LEFT OUTER JOIN sys.system_sql_modules AS ssmtr ON ssmtr.object_id = tr.object_id WHERE (tr.parent_class = 0) ORDER BY [Name] ASC

  5. Amanda says:

    I recently noticed asynch_network_io waits shoot up, and it appears that the cause is increased usage of intellisense…or there is something wrong.  It is this query – any ideas?
    (@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000))
    SELECT SCHEMA_NAME(sp.schema_id) AS [Schema],  
      sp.name AS [Name],  
      sp.object_id AS [ID],  
      CASE  
         WHEN sp.type = N'P'  THEN 1  
         WHEN sp.type = N'PC' THEN 2  
         ELSE 1  
      END AS [ImplementationType],  
      CAST(  
      CASE  
         WHEN sp.is_ms_shipped = 1 THEN 1  
         WHEN (  
         SELECT major_id  
         FROM sys.extended_properties  
         WHERE major_id = sp.object_id  
        AND minor_id = 0  
        AND class = 1  
        AND name = N'microsoft_database_tools_support'
         )  
         is not null THEN 1  
      ELSE 0  
    END AS bit) AS [IsSystemObject],  
    CAST(
    CASE  
      WHEN ISNULL(smsp.definition, ssmsp.definition) IS NULL THEN 1  
      ELSE 0  
    END AS bit) AS [IsEncrypted]  
    FROM sys.all_objects AS sp  
    LEFT OUTER JOIN sys.sql_modules AS smsp  
    ON smsp.object_id = sp.object_id  
    LEFT OUTER JOIN sys.system_sql_modules AS ssmsp  
    ON ssmsp.object_id = sp.object_id  
    WHERE (sp.type = @_msparam_0  
    OR sp.type = @_msparam_1  
    OR sp.type=@_msparam_2)  
    ORDER BY [Schema] ASC,[Name]  
      ASC

  6. Trent says:

    The intellisense on SQL 2012 is crap, it works great on 2008 but this new version has soo many issues that i'.m going to remove it and put 2008 on, thanks!

  7. Mike says:

    Thanks good sir! Ctrl+Shift+R treats me right 🙂

  8. Nageswar says:

    Download and install
    Microsoft® SQL Server® 2008 R2 Service Pack 1

  9. santosh says:

    Thx for the post…
    I faced the same issue recently & proble resolved by install SQL 2008 SP1
    Thx
    http://www.mytricks.in/2011/11/fix-sql-server-management-studio.html

  10. THEINTREPIDFOX says:

    Excellent Aaron!

  11. Warren S says:

    Also it seems they went from a character/case order to a case/character order.  Which makes it all most useless if you are in an environment that has changed their standard for CAPS to camel case.
    Example:
    TABLECUSTOMER201001
    TABLECUSTOMER201002
    TABLECUSTOMER201003
    TABLECUSTOMER201004
    All other CAP TABLES/VIEWS (could be hundreds )
    TableCustomer201101

  12. Adam Sandwiches says:

    I used redgate's for years and then changed jobs and am back to microsoft's (which is why I am on this article). I like redgate's a lot but find microsoft's a pain in the rear.

  13. Joseph says:

    What Dingo said about the period is definitely an annoyance.  I also just upgraded to 2008 R2 and am annoyed at this loss of functionality.
    Are there any solutions to this?  I had initially assumed I accidentally disabled that function somehow…why would you take it out?

  14. Dingo says:

    I've just upgraded my client tools from 2008 to 2008R2 (connecting to a 2008 R2 server) and there seem to be differences in the implementation of intillisense in Management Studio between the versions.
    Pre-upgrade (plain old 2008) to accept a suggestion from autocomplete, you could type either a period, closing square bracket or space, this for me worked pretty well.  
    Post upgrade (R2) things are worse, now the period does not accept the suggestion (it instead just appears at the end of what you've typed), the closing square bracket will accept BUT but only if you have NOT started the object name with an opening bracket!! So autocomplete entries end up looking like either dbo]. or [db]
    The functionality of the spacebar remains unchanged, but this is annoying if you're autocompleting a db/schema name as you then have to delete the space you've put in, before continuing with the table name.
    A shame, I was getting used to autocomplete and it reduces the time taken to get used to a new, complex schema at a client site (I don't have a photgraphic memory and so need either a diagram or something like autocomplete before getting up to speed).

  15. Mike Hayes says:

    Turns out that references to fields for synonyms show up underlined in red also.

  16. Brian says:

    Thanks, this helped to solve an annoying problem I was having 🙂

  17. Question: Can EZ-CAP 6.0 handle this? The dilemma is that the member only is added to EZ-CAP once the member has transferred to an IPA Central Health contracts with. But the Healthplan and Option are the same so according to the Healthplan, the benefit ac says:

    Question: Can EZ-CAP 6.0 handle this? The dilemma is that the member only is added to EZ-CAP once the member has transferred to an IPA Central Health contracts with. But the Healthplan and Option are the same so according to the Healthplan, the benefit accumulators should carry over from the first IPA (that does not contract with Central Health) to the second IPA that does. If Central Health starts the member’s accumulators off at $0 they will not be accurately tracking the member OOP totals and the deductible/OOP maximums will over-charged to the member.

  18. Louis says:

    Thank you!

  19. Paul says:

    Peters comments pointed me in the right direction but did not solve my problem.
    The issue I found was that I was granted permission to the database server as part of a windows group but my windows login had been added as a user to the database with no role.
    I believe this occured during the execution of enterprise library scripts that led to a schema being created with my login name.
    When these two items where removed intellisense resumed working.
    In summary, check all your permissions are correct before looking elsewhere for a solution

  20. Peter says:

    The short story:
    grant exec on xp_instance_regread to public
    The long story:
    We recently upgraded our user testing database to 2008. Intellisense did not work until we tried granting my user SA rights. This prompted us to search for the permission required. The MSDN articles insisted that you will be able to use Intellisense on objects that you have access to, yet I was unable to get Intellisense working on tables I could select from.
    I then went to ServerName -> Security -> Logins and tried to view the properties of my login. I got an execute denied error for xp_instance_regread. It turns out that this solves another issue in SSMS 2008 when trying to right click on non-2008 databases. Granting access to this enabled Intellisense.

  21. Dejan Grujic says:

    One of tools that offer Intellisense for SQL Server 2005 and 2000 is our DbOctopus. Nice thing is it's free until September, so there're no reasons not to give it a shot. It's not just SQL code editor, but you can see that if you visit:
    http://www.cogin.com/dboctopus/

  22. AaronBertrand says:

    Adam I understand your points, and I am actually on the same side of the fence as you.  For *ME*, IntelliSense (both the native version and RG's) simply gets in the way.  But for a lot of people, the scales tip the other way… the nuisance is worth it.

  23. cinahcaM madA says:

    Non-working Intellisense–which includes both Microsoft's and Red-Gate's implementations–wastes a lot more time than it saves. Although to be fair to RG, Microsoft's is much worse at this point in terms of getting in the way.
    Microsoft's implementation autocompletes with candidates that don't make sense, greatly increasing the number of keystrokes needed in some cases (you need to back out the autocompletion).
    RG's does not have that problem, but also doesn't support nearly as much–such as CTEs–and it has bugs where it will occasionally populate the candidate list with incorrect column names. It also often marks perfectly valid SQL as invalid, which does waste time. These comments, by the way, refer to the prerelease version of RG's newest offering in this space.
    I hope that some vendor–RG, Microsoft, or other–will solve the Intellisense problem soon. I agree that it would save a lot of time, but the offerings today don't, and for me hurt rather than help my productivity.

  24. John Clark says:

    What…"I was going to say that you should be happy if it's not working :-)"
    You people got to be kidding me…
    Use Red-Gate SQL Prompt and you will quickly grow to love intellisense…
    Just to be able to do "select * from tableA" and have it fill in the names of the columns when I want is worth the price..
    So what if intellisense not perfect.. it SAVES a huge amount of time…
    -jfc-

  25. Jared Ko says:

    There's a space on the end of the link for "When IntelliSense Is Unavailable". Should be this: http://msdn.microsoft.com/en-us/library/ms173434.aspx.
    I frequently jump between 2005 and 2008 connections and lose bracket highlighting. I'm anxious to read this link. 🙂

  26. cinahcaM madA says:

    I was going to say that you should be happy if it's not working 🙂

  27. Whitney Weaver says:

    A timely post Aaron.  I am getting the feeling that Intellisense is quickly going down in the "be careful what you wish for" column.