Converting Tables to Ansi Padding On

I am currently working with a SQL Server 2008 R2 Server and a database which uses a lot of tables that are a mixed bag of ANSI_PADDED Settings. 90% of the tables have Columns with Ansi Padding on AND columns with Ansi Padding off.

I have now been tasked with "converting" those tables so that every column uses Ansi_Padding ON.

I would like to know what is the easiest way to do this?

I am supposed to write a script which "converts" one table but in the end I am going to need a script which changes all the tables.

This can be quite a tall order. My original thinking was that you could simply add a new version of each OFF column, copy the data over, drop the old column, and rename the new column. However there are a boatload of complications that will make this tedious or downright impossible:

• constraints (including foreign keys) which will need to be disabled and/or dropped/re-created

• computed columns

• indexed views and other peripheral objects pointing at the affected table with SCHEMABINDING

• the blocking this type of operation will cause on large tables

• the mere complexity of generating an ALTER TABLE ADD script for each column, given that in 2008 R2 it is very cumbersome to derive the right data type from the metadata (it's easier in 2012, but still no picnic)

Honestly, I think the easiest thing to do would be to generate a script of the entire database:

• right-click the database > tasks > generate scripts

• if you get the intro screen, check the checkbox and click next

• choose all objects and click next

-
on the "set scripting options" tab, click "Advanced"

• Change "Types of data to script" to "schema and data"

-
on the same tab, change the output to "New query window"

In the resulting script, before running it, change all the references to the original database name to a new database name (including the file paths), and change all the ANSI_PADDING settings in the script to ON (using find/replace). Once the new database is created, you can rename the old database and rename the new one to the old name. Ultimately you can drop the old database once you've confirmed that everything is working correctly.

Warning: do not try this in SQL Server 2014 CTP2 - In the process of answering this question, I lost some of my own work, due to this bug I just filed.

StackExchange Database Administrators Q#56824, answer score: 5