Geek Answers Handbook

SQL query to get column precision value

I need a sql query that gets the precision value for specific columns. I am mainly associated with a decimal type column, and I need an accuracy value for the same.

I understand that this is possible in some versions and providers of database servers. It would be nice if you could list a few of them.

+4
source share
5 answers

Oracle:

SELECT DATA_LENGTH, DATA_PRECISION FROM ALL_TAB_COLUMNS WHERE TABLE_NAME = :TableName AND COLUMN_NAME = :ColumnName

Firebird (+ Interbase):

 SELECT rdb$field_scale, rdb$field_precision FROM rdb$relations r JOIN rdb$relation_fields rf ON rf.rdb$relation_name = r.rdb$relation_name JOIN rdb$fields fld ON rf.rdb$field_source = fld.rdb$field_name WHERE r.rdb$relation_name = :TableName AND rf.rdb$field_name = :ColumnName

MySql (not tested!):

 SELECT NUMERIC_PRECISION, NUMERIC_SCALE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = :TableName AND COLUMN_NAME = :ColumnName
+3
source

For SQL Server, you can also use NUMERIC_PRECISION in INFORMATION_SCHEMA.COLUMNS .

I suppose this could be portable.

+4
source

for sql server:

 select precision from sys.columns where name='<column_name>'

for oracle:

 select data_precision from all_tab_columns where column_name = '<columnName>'
+4
source

For SQL Server, this is:

 select object_name(object_id), name, precision from sys.columns
+1
source

For FireBird and InterBase

 SELECT RDB$FIELD_PRECISION, RDB$FIELD_SCALE FROM RDB$FIELDS WHERE RDB$FIELD_NAME = columnsDomainName
0
source

More articles:


All Articles