sql server invalid object name - but tables are listed in SSMS tables list

Question

I am attempting to create a Stored Procedure for a newly created database. However the SSMS intellisense does not recognize more than half of the tables which have been created.

For example whilst in the left hand column under tables I have a table dbo.Room, when I type "dbo." in the new query window, that table is not listed, in fact only 17 out of 37 tables are listed.

I can see no difference between the tables listed by intellisense and those not. If I manually type dbo.Room, it is underlined, with an error of

Invalid Object Name 'dbo.Room'..

Have I missed something in setting up the tables?

UPDATE: I have tried refresh of the tables list (several times)

Answer that works: `[DatabaseName].[Schema].[TableName]` [SO: Invalid Object Name sql](http://stackoverflow.com/a/24621331/1608670) — Ivan Chau, Jan 24 '16 at 13:56

score 914 · Accepted Answer · edited Oct 11 '18 at 13:18

914

Try:

Edit -> IntelliSense -> Refresh Local Cache

This should refresh the data cached by Intellisense to provide typeahead support and pre-execution error detection.

NOTE: Your cursor must be in the query editor for the IntelliSense menu to be visible.

edited Oct 11 '18 at 13:18

ShadowFlame

2,996
5
26
40

answered Sep 01 '09 at 13:44

Adam Robinson

182,639
35
285
343

Thanks, I suspect that this would have solved. I actually restarted SSMS and found that that solved. Probably should have done that in first place, but my initial thought was I had done something wrong in setup. – Richbits Sep 01 '09 at 13:54
8

Does Intellisense/SSMS refresh itself periodically? Or must this always be a manual process? (I've got my guess based on SSMS in 2005, but one can always hope.) – Philip Kelley Sep 01 '09 at 14:26
59

unbelievable that in 2012 you need to do this. Couldn't they update Intellisense every few seconds automatically? – Matthew Lock Feb 29 '12 at 05:51
8

That may require hitting the database and pulling the table/view definitions every few seconds. – jinglesthula Dec 18 '12 at 18:54
1

@jinglesthula Every few seconds might be much, but adding a new object/column seems fine to me. – anar khalilov Apr 03 '14 at 12:15
11

NOTE: Your cursor must be in the query editor for the IntelliSense menu to be visible. – bradlis7 Dec 11 '14 at 15:58
6

@jinglesthula: That would save *me* hitting the database server! – Paul Jan 16 '17 at 09:57
This doesn't appear to work on linked servers, though - at least, it didn't for me – Jon Story May 02 '18 at 13:45
Thanks, the refresh worked. It would be great if, at the least, IntelliSense would give you a light bulb like in Visual Studio to offer suggested fixes. – midoriha_senpai Aug 15 '18 at 16:05
1

Short cut combo: Ctrl+Shift+R (SSMS 17) – G Cadogan Nov 14 '19 at 18:08
This is crazy! The table was actually causing me problems with Linq within an application (not SSMS) that I was working on - returning weird results when it should have just been empty & causing exceptions. When I tried this in SMSS, the problem went away! Sorry to ruin the magic "666" upvotes but I have to add one just for that... – komodosp Jan 10 '20 at 12:49
@thanos.a Is still valid in my version 17.6. You really should add a version number that this doesn't work in. – IT Alex Feb 19 '20 at 20:14
@IT Alex I have checked it in 18.4 and the menu is valid. – thanos.a Feb 21 '20 at 07:31
1

Works in v18.5 in 2020. They still don't auto update intellisense. – MasterJoe May 26 '20 at 05:01
Thank you. Might be a setting somewhere to auto-update/refresh. Let me know anybody found that option. – Anoj Aug 14 '20 at 15:00
In VS Code, you can also do CTRL+SHIFT+P and search for "MS SQL: Refresh Intellisense Cache" to refresh (assuming mssql extension is installed of course). – user2315856 Oct 16 '20 at 13:54
It works! I wonder why there is no clear error message or a simple suggestion by SMSS to make a refresh! – Merna Mustafa Sep 09 '21 at 06:09
1

It's 2023, and we still need to do this: Version 18.12.1 – EmpathicSage Jan 26 '23 at 13:26

score 130 · Answer 2 · answered Mar 16 '18 at 06:17

130

Make sure that the selected DB is the one where the table is. I was running the Script on Master. In my case, I had to switch to hr_db.

Rookie mistake but, could help someone.

answered Mar 16 '18 at 06:17

10110

2,353
1
21
37

10

I'm not sure it's a rookie mistake to be honest, as whenever you reconnect the server it for some reason switches to "master" for no obvious reason. :) – Chris Rae Dec 28 '18 at 04:48
If that were the case and the query did not fully qualify the table names, the code wouldn't execute. I believe the OP was describing false positive error detection when the problem you've described was not a contributing factor. (Certainly, I had already ruled out the possibility when I went searching and wound up here.) – Dodecaphone Apr 15 '19 at 18:51
Exactly my issue. There's no indication from SMS that it's changed the database context, or any other clue. – Daniel Black Apr 08 '20 at 20:18
4

@ChrisRae: That's because `master` is set as the default database for your user login. Just change that using `sp_defaultdb` to another database to have the other database being selected in SSMS when you log in. (Can also be set in SSMS by `Security > Logins > your user login > Properties > Default database`) – AxD Apr 12 '20 at 23:20
1

Just to add to this. Make sure the table is referencing the same schema, i.e. "dbo." schema. – pbou Nov 24 '22 at 10:48

score 96 · Answer 3 · edited Jun 30 '17 at 13:19

96

Ctrl + Shift + R refreshes intellisense in management studio 2008 as well.

edited Jun 30 '17 at 13:19

fedorqui

275,237
103
548
598

answered Oct 19 '09 at 22:30

Zielyn

1,026
6
4

I don't have this feature ... when I press ctrl + shift + R nothing changes – Ibrahim Amer Sep 09 '15 at 17:20
4

@IbrahimAmer Your cursor must be in a query editor for the Intellisense menu option to be available. If the intellisense menu option is not available the shortcut will do nothing. – Rachael Oct 02 '15 at 13:35
It also works for T-SQL in SQL Server Management Studio 2014 – Vahid Amiri Dec 27 '16 at 18:59

score 44 · Answer 4 · answered Mar 19 '14 at 06:48

44

once you create a new SQL Server object, your newly created object does not get updated in the IntelliSence Local Cache and due to this, it shows red line underneath that object. So you just need to refresh SSMS IntelliSence Local Cache and once you refresh it, IntelliSence will automatically add newly created object in the cache and the red line will disappear. try this

Edit -> IntelliSense -> Refresh Local Cache or Ctrl + Shift + R

enter image description here

answered Mar 19 '14 at 06:48

Nagaraj S

13,316
6
32
53

1

I don't have this feature ... when I press ctrl + shift + R nothing changes – Ibrahim Amer Sep 09 '15 at 17:21
5

@IbrahimAmer Your cursor must be in a query editor for the Intellisense menu option to be available. If the intellisense menu option is not available the shortcut will do nothing. – Rachael Oct 02 '15 at 13:35

Bacon Bits · Answer 5 · 2017-05-31T14:28:46.590

19

In my case, the IntelliSense cache was listing object information for an entirely different database. If I clicked the "New Query" button in SSMS, it would open a query to my default catalog on the server and that query editor would always only use that database. Refreshing the cache didn't change anything. Restarting SSMS didn't change anything. Changing the database didn't change anything.

I ended up creating a query by right-clicking on the database I actually wanted to use and choosing "New Query" from that context menu. Now SSMS uses the correct objects for IntelliSense.

edited May 31 '17 at 14:28

answered Jan 07 '15 at 20:05

Bacon Bits

30,782
5
59
66

1

USE datbaseName GO before query also works in setting up the context – Behrooz Karjoo Jul 13 '15 at 00:03
@BehroozKarjoo Yeah, I tried that. It doesn't always seem to work. If I could reproduce it, I'd put it on Microsoft Connect or something. I'm sure they'd mark it as WONTFIX or something equally irritating, but I'd report it. – Bacon Bits Jul 13 '15 at 02:51
Restart SSMS and right click new query worked for me. – Purohit Hitesh Aug 21 '19 at 05:26

score 10 · Answer 6 · edited Mar 12 '13 at 12:03

10

Are you certain that the table in question exists?

Have you refreshed the table view in the Object Explorer? This can be done by right clicking the "tables" folder and pressing the F5 key.

You may also need to reresh the Intellisense cache.

This can be done by following the menu route: Edit -> IntelliSense -> Refresh Local Cache

edited Mar 12 '13 at 12:03

Himanshu

31,810
31
111
133

answered Sep 01 '09 at 13:41

John Sansom

41,005
9
72
84

score 7 · Answer 7 · edited Oct 30 '14 at 05:02

7

The solution is:

Click menu Query,
then click 'Change Database'.
Select your appropriate database name.

That's it.

edited Oct 30 '14 at 05:02

Infinite Recursion

6,511
28
39
51

answered Apr 23 '14 at 06:54

Tono FRL

81
1
2

score 7 · Answer 8 · answered Dec 08 '16 at 08:45

7

Same problem with me when I used this syntax problem solved.

Syntax:

Use [YourDatabaseName]
Your Query Here

answered Dec 08 '16 at 08:45

score 5 · Answer 9 · edited Mar 12 '13 at 12:04

5

Even after installing SP3 to SQL Server 2008 Enterprise this is still an "issue." Ctrl+Shift+R like everyone has been saying solved this problem for me.

edited Mar 12 '13 at 12:04

Himanshu

31,810
31
111
133

answered Nov 28 '11 at 23:13

demmith

59
1
2

I don't have this feature ... when I press ctrl + shift + R nothing changes – Ibrahim Amer Sep 09 '15 at 17:21
@IbrahimAmer Your cursor must be in a query editor for the Intellisense menu option to be available. If the intellisense menu option is not available the shortcut will do nothing. – Rachael Oct 02 '15 at 13:36

score 4 · Answer 10 · answered Mar 05 '20 at 23:52

4

In azure data studio press "cmd+shift+p" and type "intellisense", then you will see an option to refresh intellisense cache.

answered Mar 05 '20 at 23:52

pavel_orekhov

1,657
2
15
37

score 3 · Answer 11 · answered May 29 '14 at 14:45

I realize this question has already been answered, however, I had a different solution:

If you are writing a script where you drop the tables without recreating them, those tables will show as missing if you try to reference them later on.

Note: This isn't going to happen with a script that is constantly ran, but sometimes it's easier to have a script with queries to reerence than to type them everytime.

score 3 · Answer 12 · answered Mar 30 '17 at 09:23

Solved for SSMS 2016.

Had a similar problem, but Intellisense was not in Edit menu.

What seemed to fix it was turning Intellisens on and off, right click on the SQL editor and click 'Intellisense Enabled'. Right click again on 'Intellisense Enabled' to turn it back on again. Ctr Q, I also does this.

This solved the problem, and also I know get the Intellisense on the Edit menu.

KM. · Answer 13 · 2009-09-01T13:45:44.590

2

did you try: right click the database, and click "refresh"

edited Sep 01 '09 at 13:45

answered Sep 01 '09 at 13:39

KM.

101,727
34
178
212

Yes, I've tried that, I am sure it is there, I can add records and modify etc.Just did a Select * from dbo.Room and whilst I still have the underline it outputted the contents of the table. I'm gonna try a reboot. – Richbits Sep 01 '09 at 13:46
Refresh did not worked for me – Kiran Antony Jul 06 '23 at 14:29

score 2 · Answer 14 · answered Jun 23 '17 at 16:21

2

I just had to close SMSS and reopen it. I tried Refresh Local Cache and that didn't work.

answered Jun 23 '17 at 16:21

stealthysnacks

1,091
1
12
16

score 0 · Answer 15 · answered Oct 11 '19 at 06:37

I ran into the problem with : ODBC and SQL-Server-Authentication in ODBC and Firedac-Connection

Solution : I had to set the Param MetaDefSchema to sqlserver username : FDConnection1.Params.AddPair('MetaDefSchema', self.FDConnection1.Params.UserName);

The wikidoc sais : MetaDefSchema=Default schema name. The Design time code >>excludes<< !! the schema name from the object SQL-Server-Authenticatoinname if it is equal to MetaDefSchema.

without setting, the automatic coder creates : dbname.username.tablename -> invalid object name

With setting MetaDefSchema to sqlserver-username : dbname.tablename -> works !

See also the embarcadero-doc at : http://docwiki.embarcadero.com/RADStudio/Rio/en/Connect_to_Microsoft_SQL_Server_(FireDAC)

Hope, it helps someone else..

regards, Lutz

score 0 · Answer 16 · answered May 03 '20 at 16:08

0

Don't forget to create your migrations after writing the models

answered May 03 '20 at 16:08

mgPePe

5,677
12
52
85

score 0 · Answer 17 · answered Feb 03 '21 at 17:24

0

For me I had rename from

[Database_LS].[schema].[TableView]

to

[Database_LS].[Database].[schema].[TableView]

answered Feb 03 '21 at 17:24

Paul Totzke

1,470
17
33

score 0 · Answer 18 · edited Mar 05 '21 at 21:34

I was working on Azure SQL Server. For storing the data I used table values param like

DECLARE @INTERMEDIATE_TABLE3 TABLE { 
     x int;
 }

I discovered the error in writing on the queries

SELECT
    *
FROM 
    [@INTERMEDIATE_TABLE3]
WHERE 
    [@INTERMEDIATE_TABLE3].[ConsentDefinitionId] = 3

While querying the columns, it's okay to wrap it with braces like [@INTERMEDIATE_TABLE3].[ConsentDefinitionId] but when referring to just the table valued param, there should be no params. So it should be used as @INTERMEDIATE_TABLE3

So the code now must be changed to

SELECT
    *
FROM 
    @INTERMEDIATE_TABLE3
WHERE 
    [@INTERMEDIATE_TABLE3].[ConsentDefinitionId] = 3

score 0 · Answer 19 · answered Aug 10 '21 at 15:15

0

In my case, I was trying to alter a stored procedure that didn't exist in the database, after creating it and refreshing local cache, it worked

answered Aug 10 '21 at 15:15

fluid undefined

364
2
6

sql server invalid object name - but tables are listed in SSMS tables list

19 Answers19

Linked

Related