0

I need to generate an SQL script from my MS SQL Server database. In that end, I used the "Generate scripts..." tool provided by SQL Server Management, which worked.

However for one single table, some of the fields are not included in the CREATE TABLE statement. Some of them are added afterwards using ALTER TABLE XXX ADD YYY statements.

How can I ensure that all the fields are included in the CREATE TABLE statements ?

Nicolas Seiller
  • 564
  • 1
  • 10
  • 20
  • 1
    Just ensuring those are columns and not constraints. Because it uses the same opening statement `alter table x add..` for the later too. – Nikhil Vartak May 18 '16 at 14:54
  • There are both constraints and fields. But thanks, thanks to your remark I realized the columns added using ALTER are much less numerous than I first thought. – Nicolas Seiller May 19 '16 at 08:25

1 Answers1

2

Most likely there is a SET statement before the ALTER TABLE statements appear. In most cases this is caused by changing the ANSI_PADDING when adding columns. Now I'm just guessing, but can you share what SSMS generates for that table?

Something similar: SSMS "create table" sometimes scripts defaults inline, sometimes separately

RelaxedArcher
  • 96
  • 4
  • You are actually right, there are SET ANSI_PADDING OFF statements before each blocks of ALTER TABLE YYY ADD XXX statements. I tried to recreate my DB using SET ANSI_PADDING ON and re-export it afterwards, and the ALTER statements are gone, thanks a lot ! – Nicolas Seiller May 18 '16 at 15:57