Forum Discussion

Jayasurya_C01's avatar
Jayasurya_C01
New Member
2 years ago
Solved

PowerBI query

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 f...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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's avatar
amitchandak
Super User
2 years ago

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's avatar
    Jayasurya_C01
    New Member
    2 years ago

    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 🙂