Access extended properties from a dataset

Posted on 2006-04-14
Last Modified: 2010-08-05
Greetings, I added some extended properties to some columns in my sql 2005 table, but I'm having trouble accessing these properties from my dataset.  Do I need to remove my TableAdaptor compeletly and re-add it in my .xsd file in order for me to be able to access the extended propeties I added?  Here is the VB code I'm trying.

For Each DC As DataColumn In ourData.Cont.Columns
     If DC.ExtendedProperties.ContainsKey("Prop1") AndAlso DC.ExtendedProperties.Item("Prop1").ToString = "Y" Then
          ' Do something...
     End If

DC.ExtendedProperties.count is 0 for some reason
Question by:Chaffe
    LVL 6

    Expert Comment

    Try using the fn_listextendedproperty function when you create your dataset:

    --View  extended property
    SELECT * FROM ::fn_listextendedproperty(NULL,'user','dbo','table',urTable,NULL,NULL)

    fn_listextendedproperty (
        { default | [ @name = ] 'property_name' | NULL }
        , { default | [ @level0type = ] 'level0_object_type' | NULL }
        , { default | [ @level0name = ] 'level0_object_name' | NULL }
        , { default | [ @level1type = ] 'level1_object_type' | NULL }
        , { default | [ @level1name = ] 'level1_object_name' | NULL }
        , { default | [ @level2type = ] 'level2_object_type' | NULL }
        , { default | [ @level2name = ] 'level2_object_name' | NULL }


    Author Comment

    JRossi1, I'm still new to this.  can you tell me where does the syntax of the fn_listextendedproperty goes?
    LVL 6

    Accepted Solution

    When you create your dataset, use the following SQL Statement, substituting your table name etc...,

    "SELECT   * FROM   ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', 'YourTableName', 'column', default)"  The following dataset will be created:

    objtype      objname   name        value

    COLUMN      id      caption      Employee ID
    COLUMN      id      MS_Description      Employee unique ID
    COLUMN      name      caption      Employee Name
    COLUMN      name      MS_Description      Employee Full Name

    You can loop through this dataset and populate a table, DataGrid, etc... with a tables extended properties.  In the 'name' column, 'MS_Description' refers to the column description in the design view of a SQL table.

    Let me know if you need further clarification.

    Author Comment

    JRossi1,  Thanks for the clarificaiton, I got it working.  Now if I want to get the acutal table data and the properties of the columns, I would have to send 2 SELECT queries to get what I want?

    SELECT   * FROM   ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', 'MyTable', 'column', default);
    SELECT   * FROM   MyTable;
    LVL 6

    Expert Comment

    Correct....  In your last comment, the first SELECT statement would return only the table properties and the 2nd SELECT statement would return the data...

    Author Comment

    Got it.  Thanks for your help

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Training Course: Java/J2EE and SOA

    This course will cover both core and advanced Java concepts like Database connectivity, Threads, Exception Handling, Collections, JSP, Servlets, XMLHandling, and more. You'll also learn various Java frameworks like Hibernate and Spring.

    Article by: jpaulino
    XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String ( Literal, only instead of starting and ending with w…
    It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now