Change All Data Type of column from different table to new one

/ C# Notes

Things that we considered

  • Default Constraint
  • Nullable
  • Renamed Default constraint name to df_tablename_column_name format
declare @tData varchar(25) = ' decimal(14,2) ';
--1. for cols without default constraint
 SELECT N'alter table ' + OBJECT_SCHEMA_NAME(T.[object_id],DB_ID()) + '.' + T.[name] + 
     ' alter column '  + C.[name] + @tData +  
     case when C.[is_nullable] = 0 then ' 
     not null ' else ' null ' end   + '; '
 FROM sys.[tables] AS T   
     INNER JOIN sys.[all_columns] C ON T.[object_id] = C.[object_id]  
     INNER JOIN sys.[types] DTY ON C.[system_type_id] = DTY.[system_type_id] AND C.[user_type_id] = DTY.[user_type_id]   
 WHERE T.[is_ms_shipped] = 0  AND DTY.name = 'decimal' and C.default_object_id = 0
 ORDER BY T.[name], C.[column_id]
--2. for cols with default constraint
 SELECT N'alter table ' + OBJECT_SCHEMA_NAME(T.[object_id],DB_ID()) + '.' + T.[name] + ' drop constraint ' + df.[name] + '; ' +    
     'alter table ' + OBJECT_SCHEMA_NAME(T.[object_id],DB_ID()) + '.' + T.[name] + 
     ' alter column '  + C.[name]  + @tData + 
     case when C.[is_nullable] = 0 then ' 
     not null ' else ' null; ' end +
     'ALTER TABLE ' + OBJECT_SCHEMA_NAME(T.[object_id],DB_ID()) + '.' + T.[name] + ' ADD CONSTRAINT DF_'+ T.[name] +'_' + C.[name] + ' DEFAULT 0 FOR ' + c.[name] + '; '
 FROM sys.[tables] AS T   
     INNER JOIN sys.[all_columns] C ON T.[object_id] = C.[object_id]  
     INNER JOIN sys.[types] DTY ON C.[system_type_id] = DTY.[system_type_id] AND C.[user_type_id] = DTY.[user_type_id]   
     inner join sys.default_constraints df on C.default_object_id = df.[object_id]
 WHERE T.[is_ms_shipped] = 0  AND DTY.name = 'decimal'
ORDER BY T.[name], C.[column_id]