Lunarpages Web Hosting Forum

Author Topic: Change MS SQL Database Table Schema (Owner)  (Read 13115 times)

Offline belwoodcafe

  • Trekkie
  • **
  • Posts: 16
Change MS SQL Database Table Schema (Owner)
« on: June 21, 2007, 09:30:33 PM »
How to change database schema (owner) to ‘dbo’ for multiple tables at once.

Tables

Instructions:

http://www.sqlservercentral.com/columnists/kKellenberger/understandingobjectownership.asp

Change the code below in red to your old schema name. It works like a find and replace. It will change everything to ‘dbo’ schema, or you can enter a new schema name.

This is a two-step process. Run this code in the query tool of My Little Admin. It will display the results. Copy and paste the results into a new query and run it again to change all the table schema names.

Code:

declare @OldOwner varchar(100)
   declare @NewOwner varchar(100)

   set @OldOwner = 'OldOwner'
   set @NewOwner = 'dbo'

   select 'sp_changeobjectowner ''[' + table_schema + '].[' + table_name + ']'', ''' + @NewOwner + '''
   go' 
   from information_schema.tables where Table_schema = @OldOwner


Stored Procedures

Instructions:

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

Change the code below in red to your old schema name. It works like a find and replace. It will change everything to ‘dbo’ schema, or you can enter a new schema name.

This is a two-step process. Run this code in the query tool of My Little Admin. It will display the results. Copy and paste the results into a new query and run it again to change all the stored procedure schema names.

Code:

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 = 'OldUsername'

Note: Be sure to go back and read through all of your stored procedures in detail. Anywhere that references a table with the old schema must be changed to the new schema. For example: bakeryUser.myTable should be renamed to dbo.myTable.


Functions

Instructions:

http://www.enterprisedb.com/documentation/sql-alterfunction.html

Code:

ALTER FUNCTION sqrt(integer) RENAME TO newFunctionName;
ALTER FUNCTION sqrt(integer) OWNER TO newUsername;

Or, since I only had a few you can do what I did. Just view the function and copy the code. Next, delete the function and then create a new function with the same name. Finally, paste the code and change the schema name.

Be sure to change the ALTER line of code that you copied to:
CREATE FUNCTION <Schema_Name>.<Function_Name>

Here is an example:
CREATE FUNCTION dbo.FunctionName
....paste rest of code here


---------------

Here is another article with more info: http://weblogs.asp.net/owscott/archive/2004/01/30/65229.aspx

RandyT

  • Guest
Re: Change MS SQL Database Table Schema (Owner)
« Reply #1 on: June 21, 2007, 09:57:28 PM »
Thanks   :thumb: