Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2006
    Posts
    11

    Unanswered: Find column name and datatype of a given table

    Hi,

    How do we find the "column name" and "data type" of all the columns in a table. Assuming that I know the Table name or Table Object ID. I am using Microsoft SQL Server 2000.

    Thanks
    -Sudhakar

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi sudhakar_112

    Have a peak at the INFORMATION_SCHEMA tables in BoL - terribly useful.

    Specifically:
    Code:
    SELECT COLUMN_NAME, 
     DATA_TYPE, 
     CHARACTER_MAXIMUM_LENGTH
    FROM  INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'MyTable'
    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Mar 2006
    Posts
    11
    Thank you..

    using the following query lists only the columns that are acessible to the user. If the user does not have permission on a column it will not be displayed. But I want a query to display all column names irrespective of the user has any permissions to modify or select.

    Thanks
    -Sudhakar


    Quote Originally Posted by pootle flump
    Hi sudhakar_112

    Have a peak at the INFORMATION_SCHEMA tables in BoL - terribly useful.

    Specifically:
    Code:
    SELECT COLUMN_NAME, 
     DATA_TYPE, 
     CHARACTER_MAXIMUM_LENGTH
    FROM  INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'MyTable'
    HTH

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You can construct a query on the syscolumns and sysobjects tables - these are not dependent on the user permissions.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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