Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am currently working with a MSSQL 2008R2 Server and a database which uses a lot of tables that are a mixed bag of ANSI_PADDED Settings. 90% of the tables habe 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 which is the easiest way to do this? I am suposed to write a script which "converts" one table but in the end I am going to need a script which changes all the tables.

share|improve this question
1  
Do the tables have constraints (including foreign keys), triggers, and other dependencies such as indexed views and functions (especially with SCHEMABINDING)? –  Aaron Bertrand Jan 15 at 14:32
    
ANSI_Padding and Scripting Tables from SSMS will help you. –  Kin Jan 15 at 15:48

2 Answers 2

up vote 3 down vote accepted

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 ' + 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 'NULL' ELSE 'NOT 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'
share|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 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 at 12:29

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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