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.

Filed under: Blog Tagged: sql server, syndicated, T-SQL