Changing table schema in sql 2005

Question

I have a sql 2005 database and I have a table named dbo.AgencyProfile However, I want to remove the dbo prefix. How can I accomplish this?

Answer 1

Why do you need to? SQL keeps all objects in the dbo schema my default. You don't need to use the schema in your SQL statements - SELECT * FROM AgencyProfile will do fine because SQL will search the dbo schema for AgencyProfile.

You can put objects into your own schemas, but then you do need to qualify them with your schema name.

Answer 2

You can't remove the prefix/schema, but as Andy points out you don't have to use it if you don't have other schemas in the database.

Answer 3

You cannot remove the prefix/schema but you can change it by recreating the table:

CREATE TABLE [whatever].[AgencyProfile](
        [AgencyId] [int] NOT NULL DEFAULT
        [AgencyName] [nvarchar](256),
        [Field 2] [nvarchar](256),
        [Field 3] [uniqueidentifier] NOT NULL 
etc....

Answer 4

The schema is an integral part of the object: you can only remove it from code that refers to the object

There is a performance hit at compile time because it has to resolve what schema the object is in.

SELECT * FROM foobar is not the same SELECT * FROM dbo.foobar and will require a check to see what schema foobar is in. That is, it will look for [domain\user].foobar before going to dbo.foobar.

From "Execution Plan Caching and Reuse":

...

The algorithms to match new SQL statements to existing, unused execution plans in the cache require that all object references be fully qualified. For example, the first of these SELECT statements is not matched with an existing plan, and the second is matched:

SELECT * FROM Contact

SELECT * FROM Person.Contact

And for NT connections you can't specify default schema so can't avoid this

And if you want SCHEMABINDING in views etc then you have to qualify schema.

etc

It's far more than "code clutter"

Edit: after your comment elsewhere...

You have run create table with no schema so you have an object [domain\user].AgencyProfile. Of course dbo.AgencyProfile does not exist

You need to run 'ALTER AUTHORIZATION ON [domain\user].AgencyProfile TO dbo' which replaces sp_changeobjectowner