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 1

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.

Answer 2

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

Answer 3

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.

Answer 4

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

Answer 5

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.

Answer 6

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

Answer 7

The solution is:

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

That's it.

Answer 8

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

Syntax:

Use [YourDatabaseName]
Your Query Here

Answer 9

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.

Answer 10

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.

Answer 11

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

Answer 12

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

Answer 13

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.

Answer 14

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

Answer 15

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

Answer 16

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

Answer 17

For me I had rename from

[Database_LS].[schema].[TableView]

to

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

Answer 18

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