SQL query to get the precision value of a column

I开发者_Python百科 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.

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

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

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

I assume this may well be portable.

For SQL Server, it's:

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

For FireBird and InterBase

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

MySQL:

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