SQL Server COLLATION on table name

Question

For a SQL Server database with collation set to "Latin1_General_100_CS_AS" I want to run a query like:

SELECT * FROM tableName, only the table name in the database is called TableName.

SELECT * FROM TableName works fine

SELECT * FROM tableName gives error:

Msg 208, Level 16, State 1, Line 1 Invalid object name

How can I make the query case insensitive?

Answer 1

I seem to recall reading somewhere that you cannot do this.

The system database tables are always case-sensitive. As far as I know, you cannot turn that feature off, and this is not affected by your own collation settings, either - the system catalog tables are handled differently.

What you can do, however, is create a synonym:

CREATE SYNONYM tableName FOR dbo.TableName

and then your select will work again. Of course, it's not practical to create synonym for every possible case of upper-/lower-case typing - but this might be a "quick fix" for the couple most burning issues....