I am writing a script to update the datatype of a column in SQL Server on a list of tables, if the existing column are not yet of the desired type. The column should be converted from a VARCHAR to a NVARCHAR (same size, null, default... only the type changes) The database is wether in local SQL Server, or in Azure database (so very close to SQL Server)
I have something like 20 tables to modify, in something like 10 database, not always the same tables depending of the database. The problem is always the same, and without the issue I will describe below it would have been very straighforward.
To make it reusable over different databases, I am attempting to make it automatical.
To list the tables that needs to be altered is very simple :
declare @tablesWithVarchar table ( [name] varchar(500) ) insert into @tablesWithVarchar select '[' + TABLE_SCHEMA + '].[' + Table_Name + ']' from (SELECT TABLE_SCHEMA, Table_Name, Column_Name, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE 'MyColumn' and DATA_TYPE = 'varchar') as t
To generate the related ALTER is easy too :
declare @sql nvarchar(max) = 'SET XACT_ABORT ON;' select @sql = @sql + 'ALTER TABLE ' + [name] + ' ALTER COLUMN [MyColumn] NVARCHAR(500) NOT NULL; ' from @tablesWithVarchar
It will output a bunch of "ALTER TABLE" for all the tables listed in first step. I can run the result manually, or with exec sp_executesql @sql
And it works very well... As long as there is no Index on the column to alter. If there's an index, the alter fails with :
The index 'IX_xxx' is dependent on column 'MyColumn'. Msg 4922, Level 16, State 9, Line 1 ALTER TABLE ALTER COLUMN MyColumn failed because one or more objects access this column.
To find if there's one index on the column and to adapt the ALTER dynamically by doing things (?) on it seems to make the script pretty more complex.
Do you have a suggestion on how to make the ALTER TABLE achievable, despite of the index (by disabling it, or something) ? Always in an automated way, given that I don't want to hardcode the index names.
Thanks for reading