Categories
C# Notes

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

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]

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.