nvarchar to varchar.sql

-- 1. set up script tables

-- create drop unique constraint statements
declare @drop_unique_constraints table ([sql] nvarchar(max))
insert  into @drop_unique_constraints
        select 'alter table [' + tables.name + '] drop constraint ' + so.name
        from    sys.objects so
                join information_schema.constraint_column_usage iscon on so.name = iscon.constraint_name
                join sys.columns columns on so.parent_object_id = columns.object_id
                                            and iscon.column_name = columns.name
                join sys.objects tables on tables.object_id = so.parent_object_id
        where   type_name(columns.user_type_id) = 'nvarchar'
                and so.type = 'UQ' 
                
-- create add unique constraint statements
declare @add_unique_constraints table ([sql] nvarchar(max))
insert  into @add_unique_constraints
        select 'alter table [' + tables.name + '] add constraint ' + so.name + ' unique ([' + columns.name + '])'
        from    sys.objects so
                join information_schema.constraint_column_usage iscon on so.name = iscon.constraint_name
                join sys.columns columns on so.parent_object_id = columns.object_id
                                            and iscon.column_name = columns.name
                join sys.objects tables on tables.object_id = so.parent_object_id
        where   type_name(columns.user_type_id) = 'nvarchar'
                and so.type = 'UQ'


-- create change statements
declare @change_nvarchar_to_varchar table ([sql] nvarchar(max))
insert  into @change_nvarchar_to_varchar
        select  'alter table [' + so.name + '] alter column [' + sc.name
                + '] varchar(' + case when sc.max_length = -1 then 'max'
                                     else convert(varchar(4), sc.max_length/2)
                                end + ')'
        from    sys.objects so
                join sys.columns sc on so.object_id = sc.object_id
        where   type_name(sc.user_type_id) = 'nvarchar'
                and so.type = 'U'
                and so.name <> 'usd_AppliedDatabaseScript' -- tarantino table
        order by so.Name
              , sc.Name
              
select * from @drop_unique_constraints
select * from @add_unique_constraints
select * from @change_nvarchar_to_varchar
              
-- 2 remove unique constraints
declare @sql nvarchar(2000)

declare drop_uniques cursor for select * from @drop_unique_constraints
open drop_uniques
fetch next from drop_uniques into @sql
while @@FETCH_STATUS = 0 
    begin
        execute sp_executesql @sql 
        fetch next from drop_uniques into @sql
    end
close drop_uniques
deallocate drop_uniques

-- 3 change nvarchars to varchars
declare change_type cursor for select * from @change_nvarchar_to_varchar
open change_type
fetch next from change_type into @sql
while @@FETCH_STATUS = 0 
    begin
        execute sp_executesql @sql 
        fetch next from change_type into @sql
    end
close change_type
deallocate change_type

-- 4 replace unique constraints
declare add_uniques cursor for select * from @add_unique_constraints
open add_uniques
fetch next from add_uniques into @sql
while @@FETCH_STATUS = 0 
    begin
        execute sp_executesql @sql 
        fetch next from add_uniques into @sql
    end
close add_uniques
deallocate add_uniques