The other day I wanted to quickly create a table from an existing table in another database. If you are using SSMS (SQL Server Management Studio) 2005/2008, you know the quickest way to do this is to right click on the table and “Script Table As” à “Create To” à “New Query Editor Window”. When this is done, you would expect a clean create table script, with defaults, primary and foreign keys, but instead I received the following:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Table1](
[col1] [int] NULL,
[col2] [varchar](20) NULL,
[col3] [money] NULL,
[col4] [timestamp] NOT NULL,
[col5] [uniqueidentifier] NULL,
[col6] [xml] NULL
) ON [PRIMARY]
SET ANSI_PADDING OFF
ALTER TABLE [dbo].[Table1] ADD [col7] [varchar](30) NULL
ALTER TABLE [dbo].[Table1] ADD [col8] [char](25) NULL
ALTER TABLE [dbo].[Table1] ADD [col9] [nchar](40) NULL
ALTER TABLE [dbo].[Table1] ADD [col10] [nvarchar](10) NULL
SET ANSI_PADDING ON
ALTER TABLE [dbo].[Table1] ADD [col11] [varchar](30) NULL
ALTER TABLE [dbo].[Table1] ADD [col12] [char](25) NULL
ALTER TABLE [dbo].[Table1] ADD [col13] [nchar](40) NULL
ALTER TABLE [dbo].[Table1] ADD [col14] [nvarchar](10) NULL
GO
SET ANSI_PADDING OFF
GO
Why aren’t all the columns created in the initial “Create Table” statement? The answer lies in how the columns were initially added to the originating table.
When using SSMS to create objects, certain ANSI settings are set on the connection to the server. To view these ANSI settings, navigate to “Tools” à “Options” à “Query Execution” à “SQL Server” à “ANSI”
So, by default, “Set ANSI_PADDING” is set to ON for all connections to the server opened via SSMS.
One problem is that not all tools are nice enough to set these options for you automatically. If, for example, I used SSMS to create this table initially:
