How to Change DB Schema to DBO in SQL?
Last Updated :
30 Dec, 2021
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:
Share your thoughts in the comments
Please Login to comment...