How to change table / stored procedure to dbo

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

How to change table / stored procedure to dbo

Author

Topic

garrywh
Starting Member

2 Posts

Posted - 2008-10-31 : 16:26:37

Hi,

I have a database hosted on CrystalTech. When I created my tables via a script, it prefixed all my table names with my CrystalTech login name

create table account ( account_id int, account_name varchar(32))

i.e., garrywh.account
garrywh.invoice etc...

How can I remove this prefix and make it DBO?

create table [dbo].[account] (...)

This is the same as when I created my Functions. They all are prefixed with garrywh. so when I need to reference them (the functions) I have to prefix the function name with garrywh. Do I need to re-create the table with the dbo qualitifier.

I do not want to hard-code garrywh in the app...

Is there a way to "bulk" rename them to dbo or do I need to re-create the objects?

Thanks for your help.
Garry

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2008-10-31 : 16:50:33

Lookup sp_changeobjectowner in BOL. Utilize that wonderful, undocumented sp_MSFOREACHTable stored proc to cycle thorugh all tables if it's all tables that require changing.

- Edit - Sorry, the above will work in SQL 2000. Not 100% sure if it's the same in 2005.

Terry

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-10-31 : 18:22:49

Sql 2005 has to do with schema.You can check All functions and sp with sp_depends and recompile it. See Alter schema in Bol and loop with Sp_Msforeachtable.

garrywh
Starting Member

2 Posts

Posted - 2008-11-01 : 15:39:09

Thank you for the help and the nudge I was looking for.

I found a good article by searching for Alter Schema and sp_changeobjectowner

http://weblogs.asp.net/steveschofield/archive/2005/12/31/434280.aspx

Here are the commands I used...

-- To alter the stored procedures to be in the dbo schema

SELECT 'ALTER SCHEMA dbo TRANSFER ' + s.Name + '.' + p.Name FROM sys.Procedures p INNER JOIN
sys.Schemas s on p.schema_id = s.schema_id WHERE s.Name = 'garrywh'

-- To alter the tables to be in the dbo schema

SELECT 'ALTER SCHEMA dbo TRANSFER ' + TABLE_SCHEMA + '.' + TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'garrywh'

These select statements generate scripts you can apply to your database.

Cheers...

Garry

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources