Geek Answers Handbook

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

I have a rather large database in which there are many decimal columns in many tables, now the client has changed his mind and wants all numbers (decimal places) to have an accuracy of 3 dp instead of two original ones. Is there a quick way to view all tables in a database and change any decimal column in that table to have 3.dp instead of 2 dp?

db is in sql 2005.

Any help would be great.

+5
source share
3 answers

Get the columns from information_schema based on type and scale, and then change 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
+7
source

, .

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

Based on the @cmsjr suggestion and other help from stackoverflow, I came up with the following tsql, which lists all the columns whose data type is numeric and generates a script for each column that we need to change.

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
0
source

More articles:


All Articles