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

Question

Hi all,

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.

Answer 1

If you can get the table and column names this shouldn't be so bad

ALTER TABLE MyTable ALTER COLUMN MyColumn DECIMAL(#,#)

Answer 2

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