I have a rather large database that has alot of decimal columns in alot of tables, the customer has now changed their mind and wants all the numbers (decimals) to have a precision of 3 d.p. instead of the original two. Is there any quick way of going through all the tables in a database and changing any decimal column in that table to have 3.d.p instead of 2 d.p?
The db is on sql 2005.
Any help would be great.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With