"Script table as"

Question

"Script table as"

Harold Buckner

SSCarpal Tunnel

Points: 4175
More actions
March 6, 2009 at 1:56 pm

#131986

Most of the time when I use the "Script table as" option in SQL Management Studio it creates a Create Table script with all the columns. On this peticular 2005 SQL server instance it creates a create table script with the first two columns and then creates alters for the rest.
Why would it script it out like this? Any one ave a clue. Her is the script it created.
USE [SSIS_Common_Test]
GO
/****** Object: Table [dbo].[TEMP_LDO_MAILING_ADDRESSES] Script Date: 03/06/2009 15:51:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TEMP_LDO_MAILING_ADDRESSES](
[ROW_ID] [int] IDENTITY(1,1) NOT NULL,
[PROP_VALUE] [varchar](512) NULL
) ON [PRIMARY]
SET ANSI_PADDING OFF
ALTER TABLE [dbo].[TEMP_LDO_MAILING_ADDRESSES] ADD [PROPERTY_PART_OID] [varchar](10) NOT NULL
ALTER TABLE [dbo].[TEMP_LDO_MAILING_ADDRESSES] ADD [EFFECTIVE_DATE] [datetime] NOT NULL
SET ANSI_PADDING ON
ALTER TABLE [dbo].[TEMP_LDO_MAILING_ADDRESSES] ADD [NAME_PART_OID] [varchar](10) NOT NULL
ALTER TABLE [dbo].[TEMP_LDO_MAILING_ADDRESSES] ADD [MAIL_ADDL_INFO] [varchar](255) NULL
ALTER TABLE [dbo].[TEMP_LDO_MAILING_ADDRESSES] ADD [ADDRESS_NUMBER] [varchar](8) NULL
ALTER TABLE [dbo].[TEMP_LDO_MAILING_ADDRESSES] ADD [NUMBER_SUFFIX] [varchar](6) NULL
ALTER TABLE [dbo].[TEMP_LDO_MAILING_ADDRESSES] ADD [PRE_DIRECTIONAL] [varchar](20) NULL
ALTER TABLE [dbo].[TEMP_LDO_MAILING_ADDRESSES] ADD [STREET_NAME] [varchar](40) NULL
ALTER TABLE [dbo].[TEMP_LDO_MAILING_ADDRESSES] ADD [STREET_TYPE] [varchar](6) NULL
ALTER TABLE [dbo].[TEMP_LDO_MAILING_ADDRESSES] ADD [UNIT_NUMBER] [varchar](5) NULL
ALTER TABLE [dbo].[TEMP_LDO_MAILING_ADDRESSES] ADD [POST_DIRECTIONAL] [varchar](20) NULL
ALTER TABLE [dbo].[TEMP_LDO_MAILING_ADDRESSES] ADD [MUNICIPALITY] [varchar](40) NULL
ALTER TABLE [dbo].[TEMP_LDO_MAILING_ADDRESSES] ADD [PROVINCE_STATE] [varchar](2) NULL
ALTER TABLE [dbo].[TEMP_LDO_MAILING_ADDRESSES] ADD [POSTAL_ZIP] [varchar](10) NULL
ALTER TABLE [dbo].[TEMP_LDO_MAILING_ADDRESSES] ADD [PATRIOT_ACCOUNTNUMBER] [int] NULL
GO
SET ANSI_PADDING OFF

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

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

Jeffrey Williams SSC Guru Points: 90350 More actions · Answer 1

Harold Buckner (3/6/2009)
Most of the time when I use the "Script table as" option in SQL Management Studio it creates a Create Table script with all the columns. On this peticular 2005 SQL server instance it creates a create table script with the first two columns and then creates alters for the rest.
Why would it script it out like this? Any one ave a clue. Her is the script it created.
USE [SSIS_Common_Test]
GO
/****** Object: Table [dbo].[TEMP_LDO_MAILING_ADDRESSES] Script Date: 03/06/2009 15:51:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TEMP_LDO_MAILING_ADDRESSES](
[ROW_ID] [int] IDENTITY(1,1) NOT NULL,
[PROP_VALUE] [varchar](512) NULL
) ON [PRIMARY]
SET ANSI_PADDING OFF
ALTER TABLE [dbo].[TEMP_LDO_MAILING_ADDRESSES] ADD [PROPERTY_PART_OID] [varchar](10) NOT NULL
ALTER TABLE [dbo].[TEMP_LDO_MAILING_ADDRESSES] ADD [EFFECTIVE_DATE] [datetime] NOT NULL
SET ANSI_PADDING ON
ALTER TABLE [dbo].[TEMP_LDO_MAILING_ADDRESSES] ADD [NAME_PART_OID] [varchar](10) NOT NULL
ALTER TABLE [dbo].[TEMP_LDO_MAILING_ADDRESSES] ADD [MAIL_ADDL_INFO] [varchar](255) NULL
ALTER TABLE [dbo].[TEMP_LDO_MAILING_ADDRESSES] ADD [ADDRESS_NUMBER] [varchar](8) NULL
ALTER TABLE [dbo].[TEMP_LDO_MAILING_ADDRESSES] ADD [NUMBER_SUFFIX] [varchar](6) NULL
ALTER TABLE [dbo].[TEMP_LDO_MAILING_ADDRESSES] ADD [PRE_DIRECTIONAL] [varchar](20) NULL
ALTER TABLE [dbo].[TEMP_LDO_MAILING_ADDRESSES] ADD [STREET_NAME] [varchar](40) NULL
ALTER TABLE [dbo].[TEMP_LDO_MAILING_ADDRESSES] ADD [STREET_TYPE] [varchar](6) NULL
ALTER TABLE [dbo].[TEMP_LDO_MAILING_ADDRESSES] ADD [UNIT_NUMBER] [varchar](5) NULL
ALTER TABLE [dbo].[TEMP_LDO_MAILING_ADDRESSES] ADD [POST_DIRECTIONAL] [varchar](20) NULL
ALTER TABLE [dbo].[TEMP_LDO_MAILING_ADDRESSES] ADD [MUNICIPALITY] [varchar](40) NULL
ALTER TABLE [dbo].[TEMP_LDO_MAILING_ADDRESSES] ADD [PROVINCE_STATE] [varchar](2) NULL
ALTER TABLE [dbo].[TEMP_LDO_MAILING_ADDRESSES] ADD [POSTAL_ZIP] [varchar](10) NULL
ALTER TABLE [dbo].[TEMP_LDO_MAILING_ADDRESSES] ADD [PATRIOT_ACCOUNTNUMBER] [int] NULL
GO
SET ANSI_PADDING OFF

When the table was created - the first two columns were created with ANSI_PADDING ON. Then, the other columns were added with ANSI_PADDING OFF.

To recreate the table correctly, the only way to do it is the way it is scripted. Otherwise, you would have the wrong ANSI_PADDING settings for the columns.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Harold Buckner SSCarpal Tunnel Points: 4175 More actions · Answer 2

Great Thanks. I thought it might have something to do with that.