[Feature Suggestion] Allow selection of only custom columns

[marcscheuner-bfh]

More than once lately, I've wanted to get a SELECT * from a table in my CDS - but really only for the custom columns that I've added - leaving out all the default system-created columns.

Any chance a feature like that could be implemented? Something like a SELECT custom.* FROM ..... or whatever syntax makes sense - just a way to get all the custom columns for a table, without having to specify each and every single one of them ....

[MarkMpn]

I'm not aware of a standard SQL way of doing this. SELECT custom.* FROM ... would mean "list all the fields from the table 'custom'". I don't really want to be inventing my own custom SQL extensions, but if there is something in the existing T-SQL language to do this I'd be happy to take a look.

[rafek1241]

@marcscheuner-bfh Something like that:

select top 10 c.* from contact c
join account a ON c.contactid = a.primarycontactid

is working. Am I missing something?

[marcscheuner-bfh]

Something like that:

select top 10 c.* from contact c
join account a ON c.contactid = a.primarycontactid

is working. Am I missing something?

Of course this works - obviously. But this returns all columns from Account and Contact - including those gazillion of system-provided columns like "createdby", "createdon" and so forth. I was hoping Mark C. might have a nifty was of making it possible to get just the custom columns of the entities - those that I have defined / added to the entity - leaving out all the system-provided common columns

[marcscheuner-bfh]

I don't really want to be inventing my own custom SQL extensions, but if there is something in the existing T-SQL language to do this I'd be happy to take a look.

Well no - since this is really a Dynamics / CDS / Dataverse-specific feature, there's nothing in the "base" T-SQL language. Maybe a query hint would work?

SELECT * FROM MyTable WITH (CUSTOMONLY) .....

or something like this - but again: of course, this would be defining a new feature / extension, since T-SQL itself doesn't know anything about the system-provided vs. custom-defined columns of tables in CDS/Dataverse.

[nicholas-peterson]

Instead of a query hint, perhaps an option in the intellisense menu to add 'all custom fields' and it would then write out all the fields the metadata indicates are custom?