Obtaining information about table using System views

• MuditGupta

  SSCrazy

  Points: 2596

  More actions

  December 8, 2008 at 2:11 pm

  #129204

  Hi there,

  I am trying to retreive Column information from the DB. I am interested in getting Column name,its datatype and length of the column.

  Using system views, I am getting column name and its datatype but is working with obtaining length of the column.

  Does anyone know thru which system view, I can get that.

  Thanks

• Chris Harshman

  SSC-Forever

  Points: 42192

  More actions

  December 8, 2008 at 2:23 pm

  #909355

  It depends on what datatype the column is and which "system views" you are using.

  The easiest one to use is information_schema.columns

  it has character_maximum_length column for CHAR, NCHAR, VARCHAR, NVARCHAR columns, and numeric_precision, numeric_scale for DECIMAL, NUMBER columns.

  Simmilarly, in sys.columns, there is max_length for CHAR and VARCHAR, but you need to divide by 2 for NCHAR and NVARCHAR since they take 2 bytes per character. For DECIMAL and NUMBER use the precision and scale columns.

• MuditGupta

  SSCrazy

  Points: 2596

  More actions

  December 8, 2008 at 2:37 pm

  #909367

  Thanks!

  I figured out..