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
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