PowerBI query

[Jayasurya_C01]

I would like to get all database under the SQL server and all the tables under the each database of the server in powerBI like how I can see all data in sql server(SSMS). I just expecting like this format in powerbi. could you please suggest any query or any method to get data like this.

@PowerBI

[Anonymous · Accepted solution]

Hi @Jayasurya_C01 ,

Try below sql code when you connect to sql server database:

SELECT 
    d.name AS DatabaseName,
    s.name AS SchemaName,
    t.name AS TableName
FROM 
    sys.databases d
JOIN 
    sys.tables t ON d.database_id = DB_ID(d.name)
JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
ORDER BY 
    d.name, s.name, t.name;

M code:

let
    Source = Sql.Database("***", "AdventureWorksDW2014", [Query="SELECT #(lf)    d.name AS DatabaseName,#(lf)    s.name AS SchemaName,#(lf)    t.name AS TableName#(lf)FROM #(lf)    sys.databases d#(lf)JOIN #(lf)    sys.tables t ON d.database_id = DB_ID(d.name)#(lf)JOIN #(lf)    sys.schemas s ON t.schema_id = s.schema_id#(lf)ORDER BY #(lf)    d.name, s.name, t.name;#(lf)"])
in
    Source

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Jayasurya_C01 , In case you have access to read Information schema, this query can help

SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE table_type = 'BASE TABLE'

[Jayasurya_C01]

Hi Amit, yeah its really works and also i have tried this to get a databases but by using this method I can get tables for single database only(by run the query in single database query page). I just need all the database & tables under the sever.(Dynamically)
In three columns i need to view all the details of the server. 
thanks for your help 🙂