Identify Column Data Types

  • SQL_ME_RICH

    SSChampion

    Points: 11200

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

  • jonas.gunnarsson 52434

    Ten Centuries

    Points: 1134

    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

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply