Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Can some tell me how to transfer schema of "User Defined Table Type" (UDTT) in SQL 2008? Expand / Collapse
Author
Message
Posted Wednesday, September 5, 2012 8:47 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, December 12, 2013 6:13 AM
Points: 47, Visits: 67
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
Post #1354612
Posted Wednesday, September 5, 2012 9:06 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 4:16 PM
Points: 31,018, Visits: 15,456
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: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1354637
Posted Wednesday, September 5, 2012 9:15 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, December 12, 2013 6:13 AM
Points: 47, Visits: 67
Thank you for the very detailed response. It worked perfectly. The trick was "type::", that is what I didn't know about...
Post #1354650
Posted Wednesday, September 5, 2012 10:00 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 4:16 PM
Points: 31,018, Visits: 15,456
You are welcome, and it's not well documented from what I can see.






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1354709
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse