Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
T-SQL to list the data dictionary out of MDS?

Answered T-SQL to list the data dictionary out of MDS?

  • Tuesday, May 29, 2012 4:37 PM
     
     
    Sign In to Vote
    0

    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

     

All Replies

  • Thursday, May 31, 2012 5:55 PM
    Moderator
     
     Answered
    Sign In to Vote
    0

    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

     
  • Thursday, May 31, 2012 7:50 PM
     
     Proposed Answer
    Sign In to Vote
    0

    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

    • Proposed As Answer by BI Baracus Thursday, May 31, 2012 7:50 PM
    •  
     
  • Thursday, May 31, 2012 9:27 PM
     
     
    Sign In to Vote
    0
    You can use mdm.viw_SYSTEM_SCHEMA_ATTRIBUTES view for attributes information.

    Yang Wang (Microsoft SQL Server Master Data Services)

     
  • Wednesday, June 20, 2012 12:40 PM
     
     
    Sign In to Vote
    0
    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