Query the Precision and Scale of a DECIMAL column

Question

I'd like to find the numeric_precision and numeric_scale of a column which is DECIMAL as a datatype.

I tried

SELECT DATA_TYPE, COLUMN_name
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'X'

But this only gives me "decimal"

Answer 1

You can query system view information_schema.columns. I think that you are looking for columns numeric_precision and numeric_scale

select numeric_precision, numeric_scale
from information_schema.columns
where table_schema = ? and table_name = ? and column_name = ?

Demo on DB Fiddle:

create table t (col decimal(10, 2));

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

numeric_precision | numeric_scale
:---------------- | ------------:
10                |             2