Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question

migrated from stackoverflow.com May 24 '13 at 1:34

This question came from our site for professional and enthusiast programmers.

4 Answers 4

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.

share|improve this answer
4  
True true! I blogged about this here. Stop being lazy and fix the code! –  Aaron Bertrand May 24 '13 at 2:34
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

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.

share|improve this answer

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'
share|improve this answer
3  
Probably simpler to say FROM sys.tables AS t WHERE schema_id = SCHEMA_ID('erpadmin'); –  Aaron Bertrand May 24 '13 at 2:33

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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