locked
T-SQL to list the data dictionary out of MDS? RRS feed

  • Question

  • I am looking for T-SQL to create and issue a nice data dictionary for my users .  I've looked around this forum and internet and don't see one.  I am looking to get additional items such as attribute format (text, number, etc.) and length.  Obviously one can figure all this out but wondered if someone had gone thru the excersize and would make a recommendation. 

    Here is my initial SQL.  Any help would be appreciated.  Certainly if someone has a link to an SSRS report that would also be appreciated.

    SELECT ENT.Name, ENT.id AS EntityID, MIN(ATTR.SortOrder) as AttrSortOrder, ATTR.Name AS AttrName
    FROM MDS.mdm.tblEntity ENT INNER JOIN
       MDS.mdm.tblAttribute ATTR ON ENT.id = ATTR.Entity_ID
    GROUP BY ENT.Name, ENT.id, ATTR.Name


    Mel Suarez

    Tuesday, May 29, 2012 4:37 PM

Answers

  • Subscription Views are the supported way to pull data out of MDS. You shouldn't query the internal tables directly in my opinion.

    http://msdn.microsoft.com/en-us/library/ff487013.aspx

    Thanks, Jason


    Didn't get enough help here? Submit a case with the Microsoft Customer Support team for deeper investigation - http://support.microsoft.com/select/default.aspx?target=assistance

    • Marked as answer by Elvis Long Friday, June 8, 2012 2:58 AM
    Thursday, May 31, 2012 5:55 PM

All replies

  • Subscription Views are the supported way to pull data out of MDS. You shouldn't query the internal tables directly in my opinion.

    http://msdn.microsoft.com/en-us/library/ff487013.aspx

    Thanks, Jason


    Didn't get enough help here? Submit a case with the Microsoft Customer Support team for deeper investigation - http://support.microsoft.com/select/default.aspx?target=assistance

    • Marked as answer by Elvis Long Friday, June 8, 2012 2:58 AM
    Thursday, May 31, 2012 5:55 PM
  • Mel,

    You should be able to easily get what you need querying the tbl... tables.  For example,

    [tblAttribute]

    [tblEntity]

    these table link together fairly intuitively.  I don't think anything already exists to get you this.

    Gene

    Thursday, May 31, 2012 7:50 PM
  • You can use mdm.viw_SYSTEM_SCHEMA_ATTRIBUTES view for attributes information.

    Yang Wang (Microsoft SQL Server Master Data Services)

    Thursday, May 31, 2012 9:27 PM
  • thanks everyone.  Yang, I gave that view a go and it's very, very good.  I should be able to wade thru the fields/records to get my list.  Awesome.

    Mel Suarez

    Wednesday, June 20, 2012 12:40 PM