Some tables in SQL Server require [user].[table] and others don't, why is this and can I force it?

Question

As the title suggests I am confused as to why some tables in my database fall over if you do something like;

SELECT * FROM [user].[table]

And yet on another tables it works fine.

I am testing some code that will eventually be on a server that cries if you don't use [user].[table] so I would really like to force this on my machine.

Could someone explain why this happens and possible show me how to fix it.

More Info

Here is the message I get when I try and run a query using [user].[table] instead of just [table];

[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'usr.tbl'

Answer 1

The "user" bit is the schema a table belongs to

So you can have dbo.table and user.table in the same database.

By default, SELECT * FROM table will usually look for the dbo.table. However, if the login/user has a different default schema then it will look for thatschema.table

To fix it:

• You can use ALTER SCHEMA .. TRANSFER.. to fix the current setup
• Ongoing, ensure every table reference has the correct schema on CREATE, ALTER, SELECT, whatever

Also see "User-Schema Separation" on MSDN

Answer 2

What you refer to as [user] is actually something called a schema. Every user has a default schema, which means that when you are logged in as that user you can refer to the tables in the default schema without the schema prefix. One way to solve this would to be to make sure that no user has the default schema where the tables are located. Basically you can just make an emptry schema and use that as the default schema for all your users. Go to YourDatabase->Security->Users and see the properties (by right clicking) to change the default schema for your users.