24

Is there a way to temporarily suppress SQL Server Management Studio's auto-complete while typing a query? I don't want to disable auto-complete completely just say hold down some key while typing in a particular word so that it doesn't get in the way.

For example say I had the following query

SELECT Foo, Foo2 FROM SomeTable

As I type Foo and then hit space-bar SQL Server Management Studio's auto-complete kicks in and completes Foo to FooBar.

Jack
  • 341
  • 1
  • 2
  • 5
  • 6
    You will find that IntelliSense in SQL Server 2012 Management Studio is much less aggressive. I can't stand it in 2008 - if there is a column named ID it gets changed to IDENT_CURRENT. Puke. – Aaron Bertrand Jun 26 '12 at 20:29
  • 1
    @AaronBertrand - Does hitting space or comma still auto complete? I find that very annoying as I'm used to having to tab for that in other environments and 95% of the time it auto completes to something that I then need to delete the end of. – Martin Smith Jun 26 '12 at 20:34
  • @MartinSmith for space and comma, they are interpreted as they should be, unless you use the down-arrow to highlight the obvious selection (this is one keystroke), then hit space or comma. Tab picks the option SSMS thinks you meant. And the algorithm for "best choice" is 1000X better than 2008/2008 R2. The best part to me is partial match. type sys.depend and the auto-complete list is filtered to all DMVs/catalog views that contain depend. – Aaron Bertrand Jun 26 '12 at 20:39
  • @MartinSmith or, in one sentence, I think the functionality in 2012 is a lot more intuitive and works the way you would expect. – Aaron Bertrand Jun 26 '12 at 20:40
  • Thanks I was wondering how the the intelliSense worked in SQL Server 2012 Management Studio, I thought I can't be the only one who finds it to be to aggressive. –  Jun 26 '12 at 20:42
  • @AaronBertrand - Sounds much better. The partial match will be handy as often I remember part of the name of an obscure DMV but am not quite sure what the prefix is. – Martin Smith Jun 26 '12 at 20:46
  • @MartinSmith yeah I can never memorize the names of the wait stats or missing index DMVs. And some of the 2012 system object names are ridiculously long (e.g. sys.sp_describe_undeclared_parameters and sys.dm_exec_describe_first_result_set). – Aaron Bertrand Jun 26 '12 at 20:48
  • @AaronBertrand SSMS in 2014 seems to have gotten more aggressive, recently -- it really seems like a change. I remember auto-completing using tab before, but not using space... and it's doing silly things, like I try to type "IS NOT NULL", but instead, when I type the "NOT" followed by a space, it completes it with a tablename, and I get "IS NOTE_CODES". A time WASTER. BKeith's answer below (ctrl-Z) is helpful... but still a waste of time. – Doug_Ivison Feb 16 '18 at 15:56
  • 1
    @MartinSmith This will not help OP, but I think I found a solution in SSMS 2014... posted below. – Doug_Ivison Feb 16 '18 at 16:20
  • 1
    @Doug_Ivison I've posted this question quite a while ago, I'm now using SSMS 2016. I think it's better in this regard now.. – Jack Feb 16 '18 at 17:46
  • Hi, I know this is really old but I'm having this exact same issue in SQL Server Management Studio 2017 - I want it to suggest table and column names but I absolutely don't want it to automatically change every ID to IDENT_CURRENT without my explicitly saying so – pickarooney Oct 25 '21 at 10:27

5 Answers5

27

Hit ESC to close the window when done typing the word before hitting space, but if you hit ESC after 'fo' and then type 'o' it will kick in again.

Another option if you don't use it often would be to disable it (Tools->Options->Text Editor->Transact-SQL->General->Auto list members) and use CTRL+J to bring it up manually when you want to use it. Red Gate also has a product called SQL Prompt which might offer more control.

In addition there is an "Intellisense Enabled" option under the Query menu and on the Query toolbar if you want to temporarily disable/enable it.

Rohit Gupta
  • 1,626
  • 6
  • 17
  • 19
Jason Goemaat
  • 418
  • 4
  • 10
  • 1
    (This also works in other MS tools like VS.) – pst Jun 26 '12 at 20:28
  • Thanks, but its still super annoying for names that contain spaces ([Foo Bar] for example) since as you mentioned it kicks in again so you end up needing to keep hitting ESC. I'm going to leave the question open a bit longer before accepting to see if anyone know of a different solution. –  Jun 26 '12 at 20:40
  • I agree, I don't think autocomplete should be enabled in a select clause unless you already have your from tables specified. –  Jun 27 '12 at 19:05
  • 1
    Ctrl+space (the more common hotkey) also works to bring up auto-complete. – Marc Jan 27 '17 at 09:03
5

If you undo (ctrl-Z) after the wrong auto-selection appears, your original entry will be restored.

BKeith
  • 51
  • 1
  • 1
2

This will not help with SSMS 2008, but (in case others found this page, like I did), I think I found a solution in SSMS 2014:

Under the Edit pull-down menu, at the bottom, select Intellisense, and then toggle "Complete Word".

This seems to turn off implicit word completion when a space is typed, but I'm still getting the drop-downs offered for completion for columnnames, which can be selected using arrows then return/tab/space.

Doug_Ivison
  • 161
  • 1
  • 5
2

Super old thread, but in case anyone else finds themselves here in the future. I encountered this problem in SSMS v18.10 and must have pressed Ctrl + Alt + Space by accident.

This is the hotkey for Edit > IntelliSense > Toggle Completion Mode.

This alternative mode requires you to press Tab if you agree with IntelliSense's current suggestion, with space continuing as normal.

Rohit Gupta
  • 1,626
  • 6
  • 17
  • 19
Haydn Chapman
  • 21
  • 1
1

You can turn it off via Auto list members via: Tools->Options->Text Editor->Transact-SQL->General->Auto list members, however this actually causes a bug in SSMS where you get an object null error whenever you hit backspace that repeatedly annoys you.

I find Apex SQL to be quite slow, so I don't use it.

Intellisense is incredibly useful, so I have just decided to put up with the annoyance SSMS auto completing commands whenever I hit [space] or [.] until Microsoft sorts out the above bug.

James Anderson
  • 11
  • 2