Sql.Databases
The databases on a SQL Server that your sign-in may open, each with its tables and views to navigate.
Group: Databases and lakes. Power Query M has it too: a query that uses it is written as in Power BI.
Syntax
Sql.Databases(server as text, optional options as nullable record) as table| Parameter | Type | Description |
|---|---|---|
server | text | The server: host, host,port or host\instance. |
options | nullable record optional | HierarchicalNavigation: true to navigate each database by schema, as Sql.Database does; the other options are accepted and change nothing. |
Returns
table. Name, Data (the database's navigation table, as Sql.Database gives it) and Kind ("Database"), a row per database.
Remarks
- Power BI Desktop writes it when no database is named:
Sql.Databases("server"){[Name = "Sales"]}[Data]. - The system databases (master, model, msdb, tempdb), databases that are offline and those the sign-in can't open are left out; the rest are in order of name.
- Each database is signed in to as its own source; Use for every database on <server> in the sign-in keeps one sign-in for all of them.
Examples
A database, then a table
let
Source = Sql.Databases("sql.norwayanalytics.no"),
Sales = Source{[Name = "Sales"]}[Data],
Orders = Sales{[Schema = "dbo", Item = "Orders"]}[Data]
in
OrdersResult: not shown here: it needs a SQL Server and a sign-in.