I have recently been asked to create a load of reports using data from our ERP system. In order to get an understanding of which tables to use to get the required data from, it would be really useful to create a table in excel that shows a list of all the tables in the database with all the fields in each table.
There are nearly 1000 tables in the database so before I start to create the table manually I was wondering if there was a way that I can get PQ to do it for me.
Hi Chris,
Maybe the Table.Schema function will help as described here: https://blog.crossjoin.co.uk/2016/01/19/the-table-schema-function-in-power-bim/
Mynda
Thanks Mynda,
Unfortunately due to my limited knowledge of M I could only get Table.Schema to work on one table at a time. I did however manage to cobble a soultion together using List.Union and List.Transform.
Thanks again
Chris
Glad you got there in the end, Chris.
I scrapped my way of doing it when I found this
https://www.youtube.com/watch?v=icf7Hfj22vg
Just 4 lines of SQL got me what a list of all the fields whit the tables that contain them.
Select
c.table_name,
c.column_name
from Information_schema.columns c
Awesome! Thanks for sharing, Chris.
