0

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

  • There is no problem to find if a table has any indexes with the column of interest involved, and it is simple to make a script to drop it. The problem here is to recreate dropped indexes. It's quite difficult to script the index – sepupic Oct 10 '19 at 10:11
  • Yes, of course I need to preserve the existing index ! – AFract Oct 10 '19 at 11:53

Your Answer

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

Browse other questions tagged or ask your own question.