How to change tables schema OR convert.

Posted on 2007-10-04
Medium Priority
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
  • 2
LVL 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 750 total points
ID: 20012863
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
ID: 20016041

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

Expert Comment

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

exec sp_MSForEachtable 'ALTER SCHEMA [abcsql] TRANSFER ? '

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

840 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