CODEWITHSUNDEEP
sqlsql-serveroracle-database
user917670
user917670

Reputation: 871

SQL query to get the precision value of a column

I need a sql query that gets the precision value for certain columns.. I am mainly concerned with a decimal type column and I need the precision value for the same.

I realise that it is possible to do so in certain versions and vendors of database servers. It would be nice if you could list down for a few of them.

Upvotes: 7

Views: 16809

Answers (6)

Vlad Bezden
Vlad Bezden

Reputation: 89507

MySQL:

SELECT numeric_precision,
       numeric_scale
FROM information_schema.columns
WHERE TABLE_NAME = 'table_name'
    AND COLUMN_NAME = 'column_name';

Upvotes: 0

Baz1nga
Baz1nga

Reputation: 15579

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>'

Upvotes: 10

Hugh Jones
Hugh Jones

Reputation: 2694

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

Upvotes: 6

ain
ain

Reputation: 22749

For FireBird and InterBase

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

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 452978

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

I assume this may well be portable.

Upvotes: 5

Andomar
Andomar

Reputation: 238058

For SQL Server, it's:

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

Upvotes: 2

Related Questions