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.
This is a classic case of why you should specify the schema name when accessing database objects. When it is unspecified and you're trying to access an object in a non-default schema then you're going to run into the issue that you're seeing right now.
The real fix is to change your application (or whatever querying agent you have right now causing the issue) to be explicit.
When I write a query I now have to include 'erpadmin.' in front of all the table names which is confusing.
That's not confusing, that's an explicit naming convention. I recommend that you stick with that nomenclature to avoid object shuffling and inconsistencies.
As addition to @AdamWenger answer. To create scripts for transfering to anothe schema you can use following script
select 'ALTER SCHEMA dbo TRANSFER '+s.name+'.'+t.name
from sys.schemas s
join sys.tables t on t.schema_id=s.schema_id
where s.name='erpadmin'
Your trouble is likely due to how migration was done. Stuff shouldn't be attached to your user, unless you are considered the owner.
Schemas are there to help you separate tables by whatsoever makes sense. Suppose you have one table of resources for the HR departement and want a separate one for the production department, while keeping both on the same database. In that case you can have two tables named resources, one in the production schema and another in the HR schema. That's why shcemas must be specified, unless you bring stuff into the default schema.
If you're not redoing migration for some other reason, then Adam Wenger's transfer should be a wise option.
Start your command with the USE [tablename]
Your query does not have an associated database to reference and the database you are looking at is not the default for the logged in user
At the top of the query window it probably says "master"
