Display a SQL Server Table Schema in PowerBI

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 over 7 years ago and it was last updated over 7 years ago.


← Back to the Posts

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

### Why do this? This has been really useful to insert as a initial step in a Power Query M query so I can understand the table schema before I start selecting, renaming and converting column types.

Converting to a Function

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

Alternatively, this functionality is baked into Power Query M and you can just wrap the table you want the schema of in `Table.Schema` durrrrr I knew that! :P
  SCHEMA = Table.Schema(
    Sql.Database("host_address", "database_name"){[Schema="dbo",Item="TableName"]}[Data]
  )


Similar