Identify Column Data Types

• SQL_ME_RICH

  SSChampion

  Points: 11200

  More actions

  March 22, 2014 at 1:13 am

  #289627

  Comments posted to this topic are about the item Identify Column Data Types

• jonas.gunnarsson 52434

  Ten Centuries

  Points: 1240

  More actions

  April 7, 2014 at 8:37 am

  #1703817

  My favorites to retrieve column information

  -- Find nullable columns

  select TABLE_NAME, COLUMN_NAME, DATA_TYPE from INFORMATION_SCHEMA.COLUMNS

  where TABLE_NAME in (select name from sysobjects where type='U')

  and IS_NULLABLE = 'YES'

  and Left(TABLE_NAME, 3) NOT IN ('sys')

  order by TABLE_NAME;

  -- Find column with collation name set

  select TABLE_NAME, COLUMN_NAME, DATA_TYPE, CHARACTER_SET_NAME, COLLATION_NAME from INFORMATION_SCHEMA.COLUMNS

  where TABLE_NAME in (select name from sysobjects where type='U')

  and ((CHARACTER_SET_NAME is not null) or (COLLATION_NAME is not null))

  order by TABLE_NAME;

  -- Find different datatypes

  select DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, DATETIME_PRECISION, count(*) as "Count" from INFORMATION_SCHEMA.COLUMNS

  where TABLE_NAME in (select name from sysobjects where type='U' and Left(name, 3) NOT IN ('sys'))

  group by DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, DATETIME_PRECISION

  order by DATA_TYPE;

  /Jonas