Transferring Table Types

An interesting idea. I saw this question asked after I was playing with table types a bit. “Can you move a table type between schemas?”

Suppose I had two schemas:

CREATE SCHEMA OldSchema
;
GO
CREATE SCHEMA NewSchema
;
GO

In one of them, I create a table type and a procedure:

CREATE TYPE OldSchema.MyTable AS TABLE
( IDCode INT
, Location VARCHAR(200)
)
;

CREATE PROCEDURE OldSchema.MyProc 
AS
 SELECT * FROM dbo.MyLogger
;

There’s nothing fancy here. Just two objects created in one schema. I now have the need to move these to the other schema. Perhaps it’s a mistake. Perhaps I have developers working in one schema and I do integration testing in the other schema. In any case, it’s easy to move the proc with the ALTER SCHEMA syntax:

ALTER SCHEMA NewSchema TRANSFER OldSchema.MyProc
;

I can easily script something to move multiple procs, but if I do this:

ALTER SCHEMA NewSchema TRANSFER OldSchema.MyTable
;

I get this:

Msg 15151, Level 16, State 1, Line 1

Cannot find the object ‘MyTable’, because it does not exist or you do not have permission.

I know it’s there; I just created it. What’s wrong?

The problem is that this isn’t an object per se, but a type. As a result, to move a type, I need to use a different syntax:

ALTER SCHEMA NewSchema TRANSFER type::OldSchema.MyTable
;
GO

That works fine and the type has moved. The class attribute of the notation is

CLASS::Schema.Object

I haven’t found good documentation of this, but there are numerous examples in BOL that show this is how you address various “types” in SQL Server.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , . Bookmark the permalink.