Invalid object name 'information_schema.columns' in SQL Server

sql-server information-schema
ErikE picture ErikE · May 16, 2011 · Viewed 15.1k times · Source

The following query used to work:

select * from information_schema.columns where column_name like ...

But now it's throwing an error:

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'information_schema.columns'.

The application was upgraded but the view still has to exist in the database.

Answer

ErikE picture ErikE · May 16, 2011

Either:

  1. The database has a case-sensitive collation, try all upper case INFORMATION_SCHEMA.COLUMNS.
  2. Or you don't have permission.

(This question was asked by a coworker and didn't seem to be on the site so I asked and answered it.)

Related questions

How to check if a Constraint exists in Sql server?

I have this sql: ALTER TABLE dbo.ChannelPlayerSkins DROP CONSTRAINT FK_ChannelPlayerSkins_Channels but apparently, on some other databases we use, the constraint has a different name. How do I check if there's a constraint with the name FK_ChannelPlayerSkins_…

Read More
How do I find a default constraint using INFORMATION_SCHEMA?

I'm trying to test if a given default constraint exists. I don't want to use the sysobjects table, but the more standard INFORMATION_SCHEMA. I've used this to check for tables and primary key constraints before, but I don't see …

Read More
What is the most portable way to check whether a trigger exists in SQL Server?

I'm looking for the most portable method to check for existence of a trigger in MS SQL Server. It needs to work on at least SQL Server 2000, 2005 and preferably 2008. The information does not appear to be in INFORMATION_SCHEMA, but …

Read More