AdventureWorks database and dbo prefix

I have downloaded and attached AdventureWorks database to my local SQL Server 2005 Developer Edition. But unfortunately not all tables and other database object have the dbo prefix. And when I would like to run some query I have to do it against for example table named Production.Contact. I would like to run query like that:

select * from Contact

Instead of

select * from Person.Contact

I would like to understand why some tables has prefix dbo and others not. I would like all tables have dbo prefix and I could run the queries against them with their names without any additional prefixes (I mean Schema Names)开发者_C百科. Person is a Schema Name. I don't want to be obliged to have this within my query. :(

How can I achieve this?

Please help.

The dbo prefix you are seeing is called a schema. This is the same for products. The schema is somewhat like a namespace for sql server. If you want to attempt transferring the tables in the product schema to the dbo schema, you would use the following syntax:

ALTER SCHEMA dbo TRANSFER product.[someTable]

However, if the table name in the product schema already exists in the dbo schema, you will not be able to do it, since you can't have two tables with the same name. Mostly, your schema will be for organization purposes.

Addition

The reason you don't need to add the 'dbo.' schema prefix in your queries is because this is the default schema for every database. When you write queries without this schema, SQL Server just assumes you mean 'dbo'. Some best practices state that your queries should always include the schema.

The only way you may accomplish what you are asking is to associate your user account that you use to login with the Product schema. This will make the Product schema the default schema for your user login, though of course you may only have one.

The broader point I think is that it's expected to have to schema-qualify object names when writing queries in SQL 2005 and above.