Home Dashboard Directory Help

T-SQL SELECT syntax to SELECT all except a few columns by Mustansir - MSFT



Sign in
to vote
Type: Suggestion
ID: 801419
Opened: 9/18/2013 10:53:23 PM
Access Restriction: Public


I have a service which can work against multiple versions of my database schema. Across these different versions, in the tables, there are a set of columns which are the same in all versions, and then some columns which have been added as part of various feature enhancements, bug fixes, etc.
A lot of my code needs to just get back data from the tables and display it to the user in some basic format (CSV, XML, etc). However, I don't do a "SELECT *" because there are some columns (common in all versions) which are huge (varchar(MAX) and varbinary(MAX)). So, I explicitly do SELECT A, B, C etc.
Now, because of the differences in the table schemas across versions (note, the code version that can work against all these db versions is the same), every time I update the table structure my code needs to have some additional checks (if db is this version then do this SELECT; if db is this other version then do this SELECT; etc).
It would be great if there was a syntax to do "SELECT *, NOT Column1, NOT Column2, NOT Column3 FROM MyTable"
I could then use this same SELECT statement across all the different db versions for MyTable.
Sign in to post a comment.
Posted by Microsoft on 5/8/2014 at 4:48 PM
Thanks for the suggestion.
We active discourage the use of SELECT * in queries, as it can lead to problems in the application.
Always specify exactly the columns you want to select, qualify the columns, and use two-part names for the tables.

Jos de Bruijn - SQL Server PM
Posted by Microsoft on 9/19/2013 at 5:42 PM
Thank you for submitting this feedback. We are investigating the issue and will update you when we have more information.
Sign in to post a workaround.