[FIXED] How determine the scale of a decimal type column with SQL

Issue

I need to change a decimal type column with precision 16 and scale 2 to precision 16 and scale 5, for which I would do the following:

ALTER TABLE dbo.my_table ALTER COLUMN my_column DECIMAL(16, 5)

But to avoid making this change every time the application runs, I would like to read the column scale and if it is different from 5, the line above would be executed.

Is there a way to get the scale of a column of decimal type?

Solution

Is there a way to get the scale of a column of decimal type?

You can query information_schema.columns:

select column_name, numeric_precision, numeric_scale 
from information_schema.columns
where table_schema = 'dbo' and table_name = 'my_table' and column_name = 'my_column'

Answered By - GMB

Answer Checked By - Dawn Plyler (PHPFixing Volunteer)