Fetch Column Names

[VivekBhatt]

Hi All,

I want to create a collection in which i can fetch Only column names from a SQL Table 

Column 1

Column 2

Column 3

[FlowMohit]

hi @VivekBhatt 

As per my knowledge using powerapps alone, it is not possible. You need to create a flow with Execute a SQL Query(V2) action by passing this query in query option-

Flow should look like this - 

It will give you the information about table schema. Later you can transform it and pass it back to you powerapps.

[timl]

Hi @VivekBhatt 

Unfortunately, there's no way in PowerApps to retrieve column names.

There's a post in the ideas forum that you can vote for here.

https://powerusers.microsoft.com/t5/Power-Apps-Ideas/Column-Metadata/idi-p/37480 

To work around this, I would create a view that queries the system tables to retrieve the column names.

CREATE VIEW vwTableColumns
AS
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'YourTableName'

You can then add this view as a data source to your app, and use that to create your collection.

ClearCollect(yourCollection, vwTableColumns)

You can also check out @ThatAPIGuys video here.

https://www.youtube.com/watch?v=Qda7XjyfBfM&feature=youtu.be&t=780 

Here, Vivek shows how to use Flow to retrieve the first record, and to call Substitute/Replace to retrieve the column names.

[DragonLord]

 have this done please, i need it

[mhayes2]

I managed to do this using the code from this site :

https://www.matthewdevaney.com/powerapps-collections-cookbook/get-collection-column-names/

//Create a collection

ClearCollect(myTrucks5, {Year: 2020, Make: "Ford", Model: "F150", Mileage: 12343}, {Year: 2018, Make: "Chevy", Model: "Silverado", Mileage: 170567}, {Year: 2020, Make: "Ford", Model: "F150", Mileage: 98765}, {Year: 2017, Make: "Nissan", Model: "Frontier", Mileage: 123975}, {Year: 2016, Make: "GM", Model: "Yukon", Mileage: 98753} );

// Get column names

Set( myJSON, Distinct( Ungroup( MatchAll( JSON( myTrucks2, JSONFormat.IgnoreBinaryData ), "([^""]+?)""\s*:" ).SubMatches, "SubMatches" ), Value ) )

[Ondrix]

Awesome, thanks. Works flawless!👍