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

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 801419 Comments
Status Closed Workarounds
Type Suggestion Repros 0
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.