How to change tables schema OR convert.

Posted on 2007-10-04
Last Modified: 2012-02-10

I have migrate MSSQL 2000 to MSSQL 2005 but I am getting some problem about the schema.

Some  tables of  database are running  with different schema on New server for example

abcsql is db then it it's tables are running in abcsql and DBO schema so how to change dbo schema to abcsql.

I want to do this for many databases so require fastest way for it.

Question by:Sam Panwar
    LVL 142

    Accepted Solution

    the syntax to move a table from 1 schema to antoher:

    ALTER SCHEMA [abcsql] TRANSFER [dbo].[table_name]

    LVL 18

    Author Comment

    by:Sam Panwar

    I require it for all  tables of the database ,can I do it,
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    run this query to generate the script:

    exec sp_MSForEachtable 'ALTER SCHEMA [abcsql] TRANSFER ? '

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    Format Date in SQL Server Query 5 24
    Grouping within groups: SQL 5 29
    Software suggestion 12 18
    How to query LOCK_ESCALATION 4 22
    Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
    For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now