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.


Turns out that if you alter a column it takes on the ANSI_PADDING setting as of the ALTER command. Here is a quick test I did.

CREATE TABLE ansi_test
    (Col1 varchar(10),
    Col2 varchar(10),
    Col3 varchar(10)
ALTER TABLE ansi_test ADD Col4 nvarchar(10), Col5 varchar(10)
SELECT is_ansi_padded, * FROM sys.columns WHERE object_id = object_id('ansi_test')
ALTER TABLE ansi_test ALTER COLUMN Col4 nvarchar(10)
ALTER TABLE ansi_test ALTER COLUMN Col5 varchar(10)
SELECT is_ansi_padded, * FROM sys.columns WHERE object_id = object_id('ansi_test')

I also threw together a quick script to generate the alter commands (note I'm altering them to be the same data type and size as before). I am not however taking into account any type of constraints, indexes or keys that are going to get in the way. You will need to handle those columns on a one by one basis probably by dropping the index/constraint/key etc that is in the way, doing the alter, then re-creating the needed index/constraint/key etc. I also didn't have time to do a lot of testing on this so I suggest running the alters one or two at a time and making sure everything stays the same except the ANSI_PADDING setting.

SELECT 'ALTER TABLE ' + OBJECT_SCHEMA_NAME(sys.objects.object_id) + '.' + 
            sys.objects.name + ' ALTER COLUMN ' + sys.columns.name + ' ' + 
            sys.types.name + CASE WHEN sys.types.name IN ('text','ntext') THEN ''
                            WHEN sys.types.name IN ('char','varchar') 
                                THEN '('+CAST(sys.columns.max_length AS varchar(10))+')'
                            WHEN sys.types.name IN ('nchar','nvarchar') 
                                THEN '('+CAST(sys.columns.max_length/2 AS varchar(10))+')' END +
            ' ' + CASE WHEN sys.columns.is_nullable = 0 
                        THEN 'NOT NULL' ELSE 'NULL' END
FROM sys.columns
JOIN sys.types
    ON sys.columns.user_type_id = sys.types.user_type_id
JOIN sys.objects
    ON sys.columns.object_id = sys.objects.object_id
WHERE is_ansi_padded = 0
  AND sys.types.name in ('char','varchar')
  AND sys.objects.type = 'U'
| improve this answer | |
  • I wasn't sure so I was playing better safe than sorry. I ran some tests and the setting did in fact change. I've removed text, ntext, nchar and nvarchar from the script. – Kenneth Fisher Jan 15 '14 at 17:49
  • This was very helpful! While I was trying a small-scale-test I noticed something odd. The "Alter" Trick somehow only works one way. You can use this to set Ansi Padding to On/True but you can't set it to off/false. Doesn't really matter for me, just a FYI. – DLA2014 Jan 21 '14 at 12:29
  • Thanks a lot for this script! I found 2 small errors in it: first, you should use 'ALTER COLUMN' instead of just 'ALTER', and secondly, the case 'NULL' and 'NOT NULL' should be swapped. After those 2 modifications, it worked great for me, thanks again! – Bigjim Nov 26 '14 at 10:47
  • Wow, talk about some stupid mistakes. Thanks for catching those. I'm correcting the script now. – Kenneth Fisher Nov 26 '14 at 15:18

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.