Retrieve 'Source name' and/or 'Sql statement' for all tables in tabular model?

Question

Hi experts,

I am trying to get a query to retrieve all the sources for all the tables in my tabular model...

Specifically I want to get this sql code:

And if its not a query, and its a "full consumption" of a table/view, I would like to get that table/view name, is that possible?

So far I have tried

SELECT *  
 FROM $system.MDSchema_Dimensions  

But there is no such information in that DMV...

ps: The fields are either 'Source name' or 'Sql statement' (I don't hope to get all of them in one query, but perhaps 2 different queries?)

Answer 1

That information will be stored at the partition level. So $SYSTEM.TMSCHEMA_PARTITIONS should give you this. But each table can have 1 or more partitions so the TMSCHEMA_PARTITIONS view has a table ID in it and you would have to join that to TMSCHEMA_TABLES to get the table name. Unfortunately the DMX language that the DMVs use does not support joins like SQL does so you would have to do the joins using another tool (eg. Power Query in Excel)

Answer 2

Hi,

Also SELECT * FROM $SYSTEM.DISCOVER_CALC_DEPENDENCY could give you similar results but with Table Name also.
It could contain some duplictated table name due to partition.
Additionally this table gives you every dax for measure and calculated column and so on.

Regards,
Lukas