All Products
Search
Document Center

:Change the schema of an RDS SQL Server instance to dbo

Last Updated:Dec 24, 2020

Problem description

When using an RDS SQL Server instance, users may encounter situations where the instance schema is not dbo. As a result, table query errors occur and the system prompts that the object name is invalid.

Solution

Alibaba Cloud reminds you that:

  • Before you perform operations that may cause risks, such as modifying instance configurations or data, we recommend that you check the disaster recovery and fault tolerance capabilities of the instances to ensure data security.
  • You can modify the configurations and data of instances including but not limited to Elastic Compute Service (ECS) and Relational Database Service (RDS) instances. Before the modification, we recommend that you create snapshots or enable RDS log backup.
  • If you have authorized or submitted security information such as the logon account and password in the Alibaba Cloud Management console, we recommend that you modify such information in a timely manner.

The following operations use the KKK table as an example. The site is subject to the actual environment.

  1. Run the following SQL statement to view the table information:
    SELECT *
    FROM KKK
    SELECT a.name schemaName,
           b.name tableName,
           b.type_desc
    FROM sys.schemas a,
         sys.tables b
    WHERE a.schema_id = b.schema_id
    The command output is as follows. You can see that the schema is not dbo, which causes the issue.
    查看表信息
  2. Run the following command to modify the schema of a single table:
    ALTER SCHEMA dbo TRANSFER test.kkk
  3. Run the following commands to modify the schema of the table in batches.
    exec sp_msforeachtable 'alter schema dbo transfer ?'

Application scope

  • ApsaraDB RDS for SQL Server