Search code examples
sqlsql-server-2012sql-scripts

Generating SQL server script can get Create and alter statements for the same table

HI I am generating schema script for a database, but when i finish creating it and and look at the script it gives create table statement for a table but not including all column in it also it generates alter table add column statement for the same tables but for missing columns which are left in create table statement. see the attached screenshot. enter image description here

Solution

  • Assuming the question is "why does it not create all of the columns in the CREATE TABLE statement" ...

    You'll notice that between the CREATE TABLE and the ALTER statements, the value for SET ANSI_PADDING is altered. As the documentation notes, the setting's value is taken into account at the point in time when a column is created.

    There's no way to override this setting in-line with the declaration of a column.

    Since your table apparently contains a mixture of columns, some of which are defined with the setting ON and others with it defined OFF, there's no way to write a single CREATE TABLE statement that creates all of the columns in one go.