How to Change DB Schema to DBO in SQL?

In this article, we will look at two methods for changing the database schema in SQL Server to DBO. 

Schema:

SQL Schema is defined as a logical grouping of database objects. Tables, views, stored procedures, functions, indexes, and triggers are all part of database object. It is a handy tool for segregating database items for various applications, regulating access privileges, and managing database security management.

Now that we have a fundamental understanding of what schema is let’s get started. We have a database called geeks.

Step 1: Use the below SQL statement to switch the database context to geeks:

Query: 

USE geeks;

We have a table named brands (Notice: Here the table prefix is GeekSchema indicating that table is under GeekSchema schema).

Step 2: To verify the contents of the table use the below query

Query: 

SELECT * FROM
[GeekSchema].brands;

Now let’s proceed with changing the table Schema:

Method 1:  Using SSMS

Step 1: Right-click on the brands table from the object explorer window and choose Design option:

Step 2: It will opens the table designer window. Wherein we can change table properties. Click on Properties Window

Step 3: Choose the desired Scheme name from the drop down ( dbo in our case)

Step 4: Save the changes by clicking Yes on the confirmation dialogue box. Refresh the tables, we can see the schema of the brands table has been changes from GeekSchema to dbo:

Output:

Method 2: Using SQL Query

Step 1: We can also alter the Table schema using SQL Query. By using the below syntax.

Syntax:

ALTER SCHEMA TargetSchema 
TRANSFER SourceSchema.TableName; 

Use below query to alter the Schema for brands table.

Query:

ALTER SCHEMA dbo 
TRANSFER GeekSchema.brands;

Refresh the tables, we can see the schema of the brands table has been changes from GeekSchema to dbo:

Output:

sanuj8655

Follow

Improve

Article Tags :

• SQL
• SQL-Server