Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2007
    Posts
    288

    Unanswered: Change table owner

    MS SQL Server 2005

    I have a couple of tables

    12345.table1
    12345.table2

    That I would like to rename as
    dbo.table1
    dbo.table2

    Is that I schema name change or an owner change? I can't figure out how to do it.. can anyone help?

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    As a matter of fact, it is quite possible.:
    Code:
    create schema testsch
    
    create table testsch.test1
    (col1 int,
     col2 varchar(20))
    
    create table dbo.test1
    (col1 int,
     col2 varchar(20))
    
    
    insert into testsch.test1 values (1, 'hello')
    insert into testsch.test1 values (2, 'hi')
    
    alter table testsch.test1 switch to dbo.test1
    
    select *
    from testsch.test1
    select *
    from dbo.test1
    One thing to note, the target table will need to be empty, and it will probably need to have the same column definitions. Be careful of any foreign key or check constraints. Also, rebuild indexes/primary keys on any tables you do this to, just to make sure all the definitions line up properly.

    And as always, test, test, test.

  3. #3
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    sp_changeobjectowner [ @objname = ] 'object' , [ @newowner = ] 'owner'

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Ok, so there is an easier way. Shows how often this comes up ;-)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •