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 format in powerbi. could you please suggest any query or any method to get data like this.

PowerBI

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.

     

3 Replies

  • 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 🙂

  • Anonymous's avatar
    Anonymous
    Not applicable
    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.