Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest

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.


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

Comments

Leave a comment on the original post [voiceofthedba.wordpress.com, opens in a new window]

Loading comments...