Results 1 to 5 of 5

Thread: Data types

  1. #1
    Join Date
    Aug 2003
    Posts
    3

    Unanswered: Data types

    I need to export the name of certain user tables along with the names of their columns and which type the column contains ie. varchar / integer and so on. I can figure out the table and column names - but how do i retrieve information about the data-types in each column ?

    My query so far:

    -------------------------
    select obj.Name as Tbl,Col.Name as Col
    from sysobjects obj, syscolumns col
    where obj.xtype='U' and obj.Name like 'netop%' and obj.id=col.id
    -------------------------

    Thx. in advance

  2. #2
    Join Date
    May 2003
    Location
    Epsom, United Kingdom
    Posts
    42
    SELECT A.TABLE_NAME, B.COLUMN_NAME, DATA_TYPE
    FROM INFORMATION_SCHEMA.TABLES AS A
    INNER JOIN INFORMATION_SCHEMA.COLUMNS AS B
    ON A.TABLE_NAME = B.TABLE_NAME
    WHERE A.TABLE_NAME = 'yourTable'

    Very useful views and I strongly recommend that your read more about them on BOL.
    Shadow to Light

  3. #3
    Join Date
    Aug 2003
    Posts
    3
    WOW - that was fast - thanks a lot

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Beware of objects with the same name and different owners! Include a join on TABLE_SCHEMA to be safe:

    SELECT A.*, A.TABLE_NAME, B.COLUMN_NAME, DATA_TYPE
    FROM INFORMATION_SCHEMA.TABLES AS A
    INNER JOIN INFORMATION_SCHEMA.COLUMNS AS B
    ON A.TABLE_NAME = B.TABLE_NAME
    AND A.TABLE_SCHEMA = B.TABLE_SCHEMA

    blindman

  5. #5
    Join Date
    May 2003
    Location
    Epsom, United Kingdom
    Posts
    42
    Good point and well spotted.
    Shadow to Light

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •