Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Change the precision of all decimal columns in every table in the database

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.

like image 697
Jon Avatar asked Mar 31 '10 20:03

Jon


1 Answers

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
like image 91
cmsjr Avatar answered Sep 29 '22 13:09

cmsjr