Found a script on the net to change decimal column precision for MSSQL database. Can be modified to change other data type.
Change the precision of all decimal columns in every table in the database
Get the columns from information_schema based on type and scale, then alter them to have the desired scale.
declare @col sysname
declare @tbl sysname
declare @sql nvarchar(256)
declare crsFix cursor for
select table_name, Column_name from information_schema.columns
where data_type = 'decimal' and Numeric_Scale = 3
open crsFix
fetch next from crsFix into @tbl, @col
while(@@Fetch_Status = 0)
Begin
set @sql = 'Alter table [' + @tbl + '] alter column [' + @col + '] decimal(38,2) '
print @sql
exec sp_executesql @sql
fetch next from crsFix into @tbl, @col
End
close crsFix
deallocate crsFix
No comments:
Post a Comment