CODEWITHSUNDEEP
sql-server-2005
user279521
user279521

Reputation: 4807

Changing table schema in sql 2005

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?

Upvotes: 1

Views: 346

Answers (4)

gbn
gbn

Reputation: 432210

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

Upvotes: 1

Ricardo Sanchez
Ricardo Sanchez

Reputation: 6289

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

Upvotes: 1

Anders Abel
Anders Abel

Reputation: 69260

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.

Upvotes: 0

Andy Shellam
Andy Shellam

Reputation: 15535

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.

Upvotes: 1

Related Questions