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

• Vincy-856784

  Ten Centuries

  Points: 1298

  More actions

  September 5, 2012 at 8:47 am

  #266334

  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

• Steve Jones - SSC Editor

  SSC Guru

  Points: 728164

  More actions

  September 5, 2012 at 9:06 am

  #1533902

  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

  Follow me on Twitter: http://www.twitter.com/way0utwest
  Forum Etiquette: How to post data/code on a forum to get the best help
  My Blog: www.voiceofthedba.com

• Vincy-856784

  Ten Centuries

  Points: 1298

  More actions

  September 5, 2012 at 9:15 am

  #1533915

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

• Steve Jones - SSC Editor

  SSC Guru

  Points: 728164

  More actions

  September 5, 2012 at 10:00 am

  #1533969

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

  Follow me on Twitter: http://www.twitter.com/way0utwest
  Forum Etiquette: How to post data/code on a forum to get the best help
  My Blog: www.voiceofthedba.com