Wednesday, February 4, 2015

Alter Schema

Database objects and types can be moved between schemas using the ALTER SCHEMA statement.  I have never had occasion to use this statement - that's why it's on this blog.  If I blog it, I usually remember it.

To move a database object TableA from schemaOne to schemaTwo,
ALTER SCHEMA schemaTwo TRANSFER schemaOne.TableA

To move a database type TypeA from schemaOne to schemaTwo
ALTER SCHEMA schemaTwo TRANSFER TYPE::schemaOne.TypeA

This can be found on MSDN Alter Schema (Transact-SQL)