SQL SERVER – TIPS – Transfer Objects between Schemas

In this tip, I show you, how you can transfer an object between schemas using ALTER SCHEMA statement.

-- create a db test create database db_test go use db_test go --Create a schema original create schema original go -- create a schema final create schema final go -- create a table with the schema original create table original.test1 (a int) go -- checking the schema for the test1 table select schema_name(schema_id) as schema_name, name from sys.objects where name = 'test1' go /* -- Result schema_name name ------------ --------------- original test1 */ -- transfering object test1 from schema original to final -- ORIGINAL -> FINAL alter schema final transfer original.test1 go /* -- Result Command(s) completed successfully. */ -- checking the schema for the test1 table after the changes select schema_name(schema_id) as schema_name, name from sys.objects where name = 'test1' go /* -- Result schema_name name ------------ --------------- final test1 */