Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2007
    Posts
    6

    Unanswered: Export Column Descriptions

    I am trying to export a table from SQL Server 2000 with the following columns:

    TableName (the name of a table in the database)
    FieldName (the name of a field in the table)
    FieldDescription (the Description of the field)

    The field descriptions are accessed in Enterprise Manager by right-clicking on a table and choosing Design Table, on the Columns tab - "Description".

    Can this be done?
    thanks

  2. #2
    Join Date
    Feb 2003
    Location
    India
    Posts
    216
    Code:
    select cols.table_name, cols.column_name, sp.value as [description] from information_schema.columns cols
    left join sysproperties sp on sp.id = object_id('my_table') and sp.smallid=cols.ordinal_position and sp.name='MS_Description'
    where cols.table_name='my_table'
    though direct use of system tables (sysproperties) are not recommended.... there is a bit complex func for these values
    SELECT * FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', 'my_table', 'column', null)

  3. #3
    Join Date
    Jul 2007
    Posts
    6
    Thanks for that, I managed to create a view and it works.

    However, when I try to access the view from an ASP web page, the recordset only contains a fraction of the records that are contained in the view. Why would this happen?

  4. #4
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    if you are looking for documentation for your database, check out the link in my sig.

  5. #5
    Join Date
    Feb 2003
    Location
    India
    Posts
    216
    However, when I try to access the view from an ASP web page, the recordset only contains a fraction of the records that are contained in the view. Why would this happen?
    this can happen if you have a "set rowcount n" command issued or if paging is enabled for the recordset...

  6. #6
    Join Date
    Jul 2007
    Posts
    6
    I discovered the issue was that the user I am using to log into the server with for the ado connection does not have select permissions on all the tables in the database, thus the query was only showing those tables for which the user had select permission.

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Personally...I would not want my app hitting the catalog..export the data and create your own data dictionary tables that get resynched periodically
    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.

  8. #8
    Join Date
    Jul 2007
    Posts
    6
    acutally, that is a good idea. Thanks!

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by firsttube
    I discovered the issue was that the user I am using to log into the server with for the ado connection does not have select permissions on all the tables in the database, thus the query was only showing those tables for which the user had select permission.
    Are you sure this is 2000? I thought that was introduced in 2005.
    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
  •