Change one schema to another/other schema on Table @ SQL Server

Change one schema to another/other schema on Table @ SQL Server

Syntax:

ALTER SCHEMA Newschemaname Transfer OldSchemaName.tablename

Here if you want to change schema Name dbo to Person.

then issue following T-SQL

ALTER SCHEMA Person Transfer dbo.TestNames

After Issue above command you cant get older schema with table result as shown below


SELECT * FROM [dbo].[TestNames]

you can see new schema with table name gave result as see below

SELECT * FROM Person.[TestNames]

Other example:

CREATE TABLE dbo.Region 
    (Region_id int NOT NULL,
    Region_Name char(5) NOT NULL)
GO

CREATE SCHEMA Testschema;
GO

ALTER SCHEMA Testschema TRANSFER OBJECT::dbo.Region;
GO


select * from Testschema.Region

from this you can transfer schema name without data loss/drop

also know

If you want to change owner of database to sa/other user

ALTER AUTHORIZATION ON DATABASE::MyDatabase TO MyLoginUser;

(or)

Alter Authorization on Database::DatabaseName to sa