determining scale and precision of decimal/numeric datatype ??

• MuditGupta

  SSCrazy

  Points: 2596

  More actions

  January 30, 2009 at 8:13 am

  #155203

  Hi there,

  I am trying to get scale & precision of all the fields with decimal/numeric datatype.

  Is there any script to do so....?

  I tried using sys.systypes but it is showing me max scale & precision possible.. i.e. 38. But I am looking for precision/scale used in that field.

  thank you,

• MuditGupta

  SSCrazy

  Points: 2596

  More actions

  January 30, 2009 at 8:19 am

  #935581

  I got it.

  using sys.columns... precision & scale... it can be determined.

  select

  object_name(c.object_id) "Table Name"

  ,c.name "Column Name"

  ,s.name "Column Type"

  ,c.precision

  ,c.scale

  from sys.columns c

  join sys.systypes s

  on (s.xtype = c.system_type_id)

  where

  object_name(c.object_id) in

  (select name from sys.tables where name not like 'sysdiagrams')

  and s.[name] in ('decimal','numeric')

• iainthepitman

  SSCommitted

  Points: 1666

  More actions

  January 30, 2009 at 8:20 am

  #935584

  this seems to work for me

  select table_name, column_name, numeric_precision, numeric_scale

  from INFORMATION_SCHEMA.COLUMNS

  where table_name = table_name