Converting Tables to ANSI Padding On in SQL Server

configurationsql-server-2008-r2sql-server-2012sql-standard

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.

Best Answer

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.

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

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'
Related Question