Query without having to specify table's schema

I imported a bunch of tables from SQL Server 2000 to my 2008 database. All the imported tables are prefixed with my username eg: erpadmin.tablename.

In the table properties it lists 'erpadmin' as the db schema. When I write a query I now have to include 'erpadmin.' in front of all the table names which is confusing.

Current result:

select *
from erpadmin.tablename

Desired result:

select *
from  tablename

If you want to go back to using the dbo schema like you were in SQL Server 2000, you can move the table back into the dbo schema:

ALTER SCHEMA dbo TRANSFER erpadmin.tablename;

An alternative if you like having the non-dbo schema is to set your user's default schema to erpadmin then if you do not specify a schema, it will use that as default. (Members of the sysadmin fixed server role ignore the DEFAULT_SCHEMA and use dbo by default.)

ALTER USER erpadmin WITH DEFAULT_SCHEMA = erpadmin;

The two part name you have (schema.table) is a good habit to get into though, so you can be explicit with which table you are referring to. Some features require you to use a two-part name, Indexed Views are one example.

StackExchange Database Administrators Q#42955, answer score: 23