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.

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 can get the table and column names this shouldn't be so bad

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

Based on @cmsjr suggestion and other help from stackoverflow i came up with the following tsql that list all the columns whose datatype is numeric and generates a script for each and every column that we need to modify.

SELECT c.TABLE_NAME, c.column_name, c.COLUMN_DEFAULT, c.IS_NULLABLE, c.NUMERIC_PRECISION, c.NUMERIC_SCALE
, 'ALTER TABLE ' + c.TABLE_NAME + ' ALTER COLUMN ' + c.column_name  + ' NUMERIC (18,5) ' + CASE c.IS_NULLABLE WHEN 'NO' THEN ' NOT NULL' ELSE ' NULL' END  AS script
FROM INFORMATION_SCHEMA.columns cs
INNER JOIN INFORMATION_SCHEMA.tables t ON t.table_name = c.table_name
WHERE c.data_type like 'numeric' AND t.table_type = 'base table'
--AND c.NUMERIC_PRECISION in (9,18) AND c.NUMERIC_SCALE = 2