Results 1 to 15 of 15
  1. #1
    Join Date
    Apr 2004
    Posts
    49

    Unanswered: How can I retrieve Description and other column information?

    Hi, all..
    I want to know the query to retrieve Column information that we can see from table Design view of Enterprise manager, such as Column name, Pk or not, FK table, Data Type, Null or not, Description(Specially Descrition).

    Is there any sp for this or any query for this?

    Thank you all...
    MCSD .NET, SCJP, SCJWD

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    type

    sp_help tablename
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    select * from INFORMATION_SCHEMA.TABLES
    select * from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
    select * from INFORMATION_SCHEMA.KEY_COLUMN_USAGE

    See BOL for more information about schema.

  4. #4
    Join Date
    Apr 2004
    Posts
    101
    You can also use
    INFORMATION_SCHEMA.columns
    cheers
    -ss

  5. #5
    Join Date
    Aug 2003
    Location
    Kingston, Ontario
    Posts
    106
    I too would like to get at the Description and I tried all of the above but Description doesn't seem to be there. I looked at all the INFORMATION_SCHEMA views and found nothing. I checked the system tables for the DB in question and found nothing. Could the Description be something internal to EM that isn't available to clients?

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    What do you mean by description?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Aug 2003
    Location
    Kingston, Ontario
    Posts
    106
    I assume the Column Description you can enter in EM when designing a table. It's the first item on the Columns tab.

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    OK...I usually maitain my data dictionary separatley....

    You need to look up

    fn_listextendedproperty

    Never really used it.....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Quote Originally Posted by grahamt
    I assume the Column Description you can enter in EM when designing a table. It's the first item on the Columns tab.
    Check this table:

    select * from sysproperties

  10. #10
    Join Date
    Aug 2003
    Location
    Kingston, Ontario
    Posts
    106
    Yup... Works fine.

    Thanks Brett..

  11. #11
    Join Date
    Aug 2003
    Location
    Kingston, Ontario
    Posts
    106
    select * from sysproperties
    Works as well but may take extra coding to figure out the id and smallid so the data for the correct column can be identified.

    Thanks...

  12. #12
    Join Date
    Aug 2003
    Location
    Kingston, Ontario
    Posts
    106
    It works!!! Assuming a Table name of 'KB' and a column named 'Title' this gets the description.

    Code:
    declare @tbid integer
    declare @colid integer
    
    Select @tbid=id from sysobjects where xtype='u' and name='KB'
    Select @colid = colid from syscolumns Where id=@tbid And name='Title'
    select value from sysproperties where id=@tbid and smallid=@colid and name='MS_Description'
    Thanks snail for the pointer to sysProperties.

  13. #13
    Join Date
    Apr 2004
    Posts
    49
    Thanks all..
    I think we have to know sysproperties table joined with which table..
    MCSD .NET, SCJP, SCJWD

  14. #14
    Join Date
    Aug 2003
    Location
    Kingston, Ontario
    Posts
    106
    Can't help with the JOIN. There are three tables here and I haven't figured out how to JOIN two yet!

  15. #15
    Join Date
    Apr 2004
    Posts
    49

    Final solution

    Now I am finishing ...
    This works! the following three tables joined gives what I asked.
    I hope this helps all...
    Thank you all for considering

    Use Northwind
    SELECT sysobjects.name AS [Table], syscolumns.name AS [Column], sysproperties.[value] AS Description
    FROM (sysobjects INNER JOIN syscolumns
    ON syscolumns.id = sysobjects.id) LEFT JOIN sysproperties
    ON sysproperties.smallid = syscolumns.colid AND
    sysproperties.id = syscolumns.id
    where sysobjects.name = 'Orders'

    -- PS
    sysobjects.name : table Name (WHERE sysobjects.xtype = 'U')
    sysobjects.id : table ID
    sysproperties.smallid : Column id
    sysproperties.[value] : Description

    You can add following
    WHERE sysobjects.xtype = 'U' xtype='U' means only select Table object
    MCSD .NET, SCJP, SCJWD

Posting Permissions

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