Scripting out user-defined types

When tables are scripted out in VS the underlying types are produced rather than the user-defined types. Is there any way to change this behavior We would rather not lose that information if possible.

Visual Studio Team System32

You mean you are using UDDT's (sp_addtype) like the tid type in the pubs sample database These should not get converted, I just checked if they are when importing pubs for example and it works as expected.

Do you see the UDDT showing up in the Schema View

-GertD
"DataDude" Development Manager

Sorry for the delay - I was on vacation.

The problem occurs when creating scripts from inside VS.

The UDDT is created as follows:

CREATE TYPE UserIdType FROM int NOT NULL

The table is created as follows:

CREATE TABLE Users (UserId UserIdType, Name varchar(50))

Then script out the "CREATE" for the table in VS (Right-click on the table in the Server Explorer and select "Generate Create Script to Project"). It will look as follows:

/****** Object: Table [dbo].[Users] Script Date: 08/28/2006 10:18:17 ******/

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Users]') AND type in (N'U'))

DROP TABLE [dbo].[Users]

GO

/****** Object: Table [dbo].[Users] Script Date: 08/28/2006 10:18:17 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Users]') AND type in (N'U'))

BEGIN

CREATE TABLE [dbo].[Users](

[UserId] [int] NOT NULL,

[Name] [varchar](50) NULL

) ON [PRIMARY]

END

GO

SET ANSI_PADDING OFF

GO

Note that the UserId's type is now "[int] NOT NULL" rather than "UserIdType", so the UDDT is lost.

The issue you are describing is an expected behavior for scripting out a table that is referring to a UDDT. If you notice, it did assign the proper type and NOT NULL to the UserId column in the scripted CREATE TABLE statement.

The reason it does this is that the script is meant to stand alone and doesn't contain any dependencies on the UDDT pre-existing in the database where you may run this script.

Do you get the same result if you script the entire database instead of just the table

Andrew

Andrew,

Your reasoning for why it is not scripted out as the UDDT in VS 2005 is reasonable. However, the SQL Server Management Studio, when scripting out the create, uses the UDDT. It appears that the two development groups were not consistent in their reasoning.

Ken

This is actually Server Explorer functionality, which is present in VS 2005 and we can not and did not change this behavior. If you would look at the script that would get generated when you import the schema in to the DBProj it should show the UDDT reference in the CREATE TABLE.

-GertD