我需要将精度为16且小数位数为2的十进制类型列更改为精度为16且小数位数为5，为此我将执行以下操作：
ALTER TABLE dbo.my_table ALTER COLUMN my_column DECIMAL(16, 5)
但是为了避免每次应用程序运行时都进行这种更改，我想读取列比例，如果它与5不同，则执行上面的行。有没有办法得到十进制类型列的小数位数？
bz4sfanl1#
有没有办法得到十进制类型列的小数位数？您可以查询 information_schema.columns :
information_schema.columns
select column_name, numeric_precision, numeric_scale from information_schema.columnswhere table_schema = 'dbo' and table_name = 'my_table' and column_name = 'my_column'
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'
emeijp432#
你可以从 sys 物体：
sys
SELECT ct.name AS DataType, c.precision, c.scaleFROM sys.schemas s JOIN sys.tables t ON s.schema_id = t.schema_id JOIN sys.columns c ON t.object_id = c.object_id JOIN sys.types ct ON c.system_type_id = ct.system_type_idWHERE s.name = N'Your Schema' AND t.name = N'Your Table' AND c.name = N'Your Column';
SELECT ct.name AS DataType,
c.precision,
c.scale
FROM sys.schemas s
JOIN sys.tables t ON s.schema_id = t.schema_id
JOIN sys.columns c ON t.object_id = c.object_id
JOIN sys.types ct ON c.system_type_id = ct.system_type_id
WHERE s.name = N'Your Schema'
AND t.name = N'Your Table'
AND c.name = N'Your Column';
2条答案
按热度按时间bz4sfanl1#
有没有办法得到十进制类型列的小数位数？
您可以查询
information_schema.columns:emeijp432#
你可以从
sys物体：