Reputation: 7634

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

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)

Upvotes: 438

Answers (21)

DayByDay

Reputation: 84

Make sure you're running a CREATE PROCEDURE query as opposed to an ALTER PROCEDURE query. This has happened to me multiple times in copy/paste situations.

When you try to ALTER a Stored Procedure that doesn't exist, you end up with the same error this question asker encountered.

Simple example, below:

Right:

CREATE PROCEDURE [dbo].[ReportWidgetGetSubmissionsForExport]

AS BEGIN

SELECT * 
FROM [dbo].ReportWidgets
ORDER BY CreatedDate

END

Wrong:

ALTER PROCEDURE [dbo].[ReportWidgetGetSubmissionsForExport]

AS BEGIN

SELECT * 
FROM [dbo].ReportWidgets
ORDER BY CreatedDate

END

Upvotes: 0

Timothy

Reputation: 50

In my case, the solution turned to be changing the user/role that I was trying to query it with, because my role didn't have access to it.

As a side note: the error message was encountered during the replication through cdc.

Upvotes: 0

Adam Robinson

Reputation: 185703

Try:

Edit -> IntelliSense -> Refresh Local Cache or use the hotkey CTRL+SHIFT+R

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.

Upvotes: 940

fluid undefined

Reputation: 418

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

Upvotes: 0

Sriharsha g.r.v

Reputation: 510

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

Upvotes: 0

Paul Totzke

Reputation: 1440

For me I had rename from

[Database_LS].[schema].[TableView]

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

Upvotes: 0

mgPePe

Reputation: 5907

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

Upvotes: 0

Pavel Orekhov

Reputation: 2204

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

Upvotes: 4

Lutz

Reputation: 21

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 !

Hope, it helps someone else..

regards, Lutz

Upvotes: 0

10110

Reputation: 2695

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.

Upvotes: 143

Zielyn

Reputation: 1066

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

Upvotes: 100

stealthysnacks

Reputation: 1141

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

Upvotes: 2

Bacon Bits

Reputation: 32230

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.

Upvotes: 19

Eric Yeoman

Reputation: 1036

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.

Upvotes: 3

user5093161

Reputation:

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

Syntax:

Use [YourDatabaseName]
Your Query Here

Upvotes: 7

Tono FRL

Reputation: 81

The solution is:

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

That's it.

Upvotes: 7

Troy Loberger

Reputation: 347

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.

Upvotes: 3

Nagaraj S

Reputation: 13484

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

Upvotes: 44

demmith

Reputation: 59

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.

Upvotes: 5