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.)


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.

| improve this answer | |

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.

| improve this answer | |
  • 3
    Another reason for always using two-part names is to avoid the situation where multiple users execute the same code (for example select ... from table5 ;) and get different results. This is bad for plan caching and also bad for troubleshooting (cue support person, "that query runs fine here"). Also, schemabinding, which is required for indexing of functions and views, requires two part names. TLDR: stop being lazy - use two part names. – Greenstone Walker May 24 '13 at 2:43

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'
| improve this answer | |

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.

| improve this answer | |

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"

| improve this answer | |
  • 3
    You mean [database_name], right? – dezso Jan 26 '17 at 20:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.