Sql-server – Get list of all tables and columns “touched” by application

If this is just for development/testing, you can get a license for Developer Edition (free for 2014+ and ~$50 for 2012 and lower). and use snapshots, since Developer Edition supports all of the features supported in Enterprise Edition. The only difference is licensing - you cannot use Developer Edition in production.

You're not going to be able to cook something up that's much simpler or faster than reverting from a database snapshot. But do some research - you want to be aware of things like this.

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'