This was useful when building a subsequent M Query and needed to know all available table column, types and keys
me@jaykilleen.com wrote this about 7 years ago and it was last updated about 7 years ago.
EDIT: I was enlightened to an easier way to do this which is at the bottom.
I converted this query from a SQL query found on StackOverflow
let
SCHEMA = Sql.Database("host_address", "database_name",
[Query="
SELECT
c.name As 'Column Name',
t.Name As 'Data type',
c.max_length As 'Max Length',
c.precision ,
c.scale ,
c.is_nullable,
ISNULL(i.is_primary_key, 0) 'Primary Key'
FROM
sys.columns AS c
INNER JOIN
sys.types AS t ON c.user_type_id = t.user_type_id
LEFT OUTER JOIN
sys.index_columns AS ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
LEFT OUTER JOIN
sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
WHERE
c.object_id = OBJECT_ID('TableName')
",
CreateNavigationProperties=false
]
)
in
SCHEMA
For me this returned a table like:
| Column Name | Data type | Max Length | precision scale | is_nullable | Primary Key |
|---|---|---|---|---|---|
| Key | int | 4 | 10 | 0 | FALSE |
| Code | varchar | 3 | 0 | 0 | TRUE |
| Name | varchar | 40 | 0 | 0 | TRUE |
| PostCode | tinyint | 1 | 3 | 0 | TRUE |
You can easily reuse this in other projects or clean up your other queries by converting this to a function like:
let
Source = (TableName as text) => let
Source =
"
SELECT
c.name As 'Column Name',
t.Name As 'Data type',
c.max_length As 'Max Length',
c.precision ,
c.scale ,
c.is_nullable,
ISNULL(i.is_primary_key, 0) 'Primary Key'
FROM
sys.columns AS c
INNER JOIN
sys.types AS t ON c.user_type_id = t.user_type_id
LEFT OUTER JOIN
sys.index_columns AS ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
LEFT OUTER JOIN
sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
WHERE
c.object_id = OBJECT_ID(" & "'" & TableName & "'" & ")
"
in
Source
in
Source
You can then invoke this function like GET_TABLE_SCHEMA("dimWarehouse") which can simplify our original query to:
let
SCHEMA = Sql.Database("host_address", "database_name", [Query=GET_TABLE_SCHEMA("TableName")]
)
in
SCHEMA
SCHEMA = Table.Schema(
Sql.Database("host_address", "database_name"){[Schema="dbo",Item="TableName"]}[Data]
)