change old schema name into new schema name

  • Write a SQl or change the Schema Names of all the tables who has a Schema Name of DBO to Training .

    E.x You have Many tables created with Schema of DBO. Now i dont want to see any tables with Schema of DBO. All the table names should be not dbo. but should be training.

  • Unless all your code is written without schema names in queries, updates, etc., you'll need to do far more than just rename them all.

    I don't think there's a way to just rename a schema and have it work. sp_rename only works on objects that are in sys.objects, and schemas aren't one of those.

    You're probably going to have to rebuild the database pretty much from the ground up. I could be wrong, but I will be surprised if I am.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • AFAIK, changing the schema name of an object cannot be done: you would have to DROP the object and then recreate it with the new schema name.

    And as Gus points out, then you have to find all of the reference to the object and change their SQL code too.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • You can use

    ALTER SCHEMA Trainning TRANSFER dbo.tablename;

    You will lose the permissions though 🙁


    * Noel

  • Cool tip, Noel! I stand corrected... 🙂

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • noeld (2/10/2009)


    You can use

    ALTER SCHEMA Trainning TRANSFER dbo.tablename;

    You will lose the permissions though 🙁

    Didn't know about that one. But that still leaves you with a whole database of code to review.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Try this:

    /* Schema fix all tables.sql

    By: MJW 02/05/2010

    Desc: change all tables from one schema to another

    */

    declare @sql varchar(8000), @table varchar(1000), @oldschema varchar(1000), @newschema varchar(1000)

    set @oldschema = 'dbo'

    set @newschema = 'Training'

    while exists(select * from sys.tables where schema_name(schema_id) = @oldschema)

    begin

    select @table = name from sys.tables

    where object_id in(select min(object_id) from sys.tables where schema_name(schema_id) = @oldschema)

    set @sql = 'alter schema ' + @newschema + ' transfer ' + @oldschema + '.' + @table

    exec(@sql)

    end

    edit: changed schema strings to fit the original post for clarity

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply