In SQL Server, how to generate table script as a single statement?

dexterama

I am attempting to put a database into the Data-Tier application model, and the wizards (being MS Wizards) are painful and lack error reporting. So, I'm trying to export my database as scripts, and import the scripts into the DAC model. Here's the problem: over the years, our tables have undergone modifications. What I don't get is why SQL Server seems to have to regenerate a table from script in sequence (here's a sample)

CREATE TABLE [dbo].[SampleUserMaster](
[SampleUserMasterKey] [dbo].[udt_keyfield] IDENTITY(1,1) NOT NULL,
[UserKey] [dbo].[udt_keyfield] NULL,
[rowguid] [uniqueidentifier] ROWGUIDCOL NULL,
[SampleKey] [dbo].[udt_keyfield] NOT NULL,
[UserMasterKey] [dbo].[udt_keyfield] NULL,
[AddressRefusedUnknownFlag] [dbo].[udt_logical] NULL,
[SampleCompletedFlag] [dbo].[udt_logical] NULL,
[SampleCompletedDate] [datetime] NULL,
[IncomingSampleActionDate] [datetime] NULL,
[IncomingSampleActionCodesKey] [dbo].[udt_keyfield] NULL,
[IncomingSampleActionCode] [dbo].[udt_sharedcode] NULL,
[IncomingSampleActionDescription] [dbo].[udt_sharedcodedescription] NULL,
[LockSession] [uniqueidentifier] NULL,
[LockTime] [datetime] NULL,
[LockVersion] [timestamp] NOT NULL,
[WWClearanceCodeKey] [dbo].[udt_keyfield] NULL
) ON [PRIMARY]
SET ANSI_PADDING OFF
ALTER TABLE [dbo].[SampleUserMaster] ADD [WWClearanceCode] [dbo].[udt_sharedcode] NULL
ALTER TABLE [dbo].[SampleUserMaster] ADD [WWClearanceDescription] [dbo].[udt_sharedcodedescription] NULL
ALTER TABLE [dbo].[SampleUserMaster] ADD [WWClearanceCodeDateTime] [datetime] NULL
ALTER TABLE [dbo].[SampleUserMaster] ADD [WWClearanceCodeAuthorityInvolvedKey] [dbo].[udt_keyfield] NULL
ALTER TABLE [dbo].[SampleUserMaster] ADD [IsElligibleWorkRelease] [dbo].[udt_logical] NULL
ALTER TABLE [dbo].[SampleUserMaster] ADD [DepartmentKey] [dbo].[udt_keyfield] NOT NULL
ALTER TABLE [dbo].[SampleUserMaster] ADD [CreatedDateTime] [datetime] NOT NULL
ALTER TABLE [dbo].[SampleUserMaster] ADD [ModifiedDateTime] [datetime] NOT NULL
ALTER TABLE [dbo].[SampleUserMaster] ADD [VisitorListLastChangedDateTime] [datetime] NULL
ALTER TABLE [dbo].[SampleUserMaster] ADD [IsSealed] [bit] NULL
CONSTRAINT [PK_SampleUserMasterkey] PRIMARY KEY CLUSTERED
(
[SampleUserMasterKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

Note the alter table statements for each column added over time - why is th

PIEBALDconsult

Try using the SqlPubWiz to generate the scripts. P.S. Oh, and there's an article on here that generates scripts.

dexterama

Thanks for the tip; trying it now. (SQL Pub Wiz)

Think of how stupid the average person is, and realize half of them are stupider than that. - George Carlin

Mycroft Holmes

I'm curious, never having used UDTs, what requires you to use so many and what is in the types. Do you think the UDTs affect performance, normally I would not even ask but you seem to have a hoarde of them!

Never underestimate the power of human stupidity RAH