Can some tell me how to transfer schema of "User Defined Table Type" (UDTT) in SQL 2008?

  • I have cloned a database from another database and able to change the schema for all the objects except the UDTTs. Now I got stuck here. I know I can do it manually by removing the reference from the dependent objects and dropping the UDTT and re-creating with the new schema. But it is too cumbersome and I am pretty sure there should be a better way of doing this.

    So, I thought some of you might have that better answer for me.

    I greatly appreciate your quick and generous answer/help.

    Thanks,

    Vincent

  • Use ALTER SCHEMA, with the type::schema.object

    http://msdn.microsoft.com/en-us/library/ms173423.aspx

    CREATE SCHEMA OldSchema

    ;

    GO

    CREATE SCHEMA NewSchema

    ;

    GO

    CREATE TYPE OldSchema.MyTable AS TABLE

    ( IDCode INT

    , Location VARCHAR(200)

    )

    ;

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

    ;

    ALTER SCHEMA NewSchema TRANSFER OldSchema.MyProc

    ;

    ALTER SCHEMA NewSchema TRANSFER type::OldSchema.MyTable

    ;

    GO

  • Thank you for the very detailed response. It worked perfectly. The trick was "type::", that is what I didn't know about... 🙂

  • You are welcome, and it's not well documented from what I can see.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply