SELECT "all except" columns

Ok so here's the deal. I'm working with tables at work that were not well designed. These tables are converted flat files (from mainframe) so one table may have 50 or more columns of information (which may or may not be related). Lets say I want to pull some records from such a table but out of the 40 columns, I want all of them except 2 (those 2 being an SSN and/or a crypted password; something along those lines). Is there a quick way to say:

SELECT * (-SSN, -password) FROM [table]

instead of having to manually write out

SELECT col1, col2, col3... col37, col38  ?
LVL 15
MMDeveloperAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
>Is there a quick way to say:
no.  at least not with sql itself.

however, you could use a sql to query for all the column names of the table/view... and just make the list of all the columns you want.
0
 
sdstuberConnect With a Mentor Commented:
yes, I'm afraid so.  There is no "all except" syntax in SQL
0
 
MMDeveloperAuthor Commented:
afterthought... lets say that the 2 columns I dont want are called "ssn" and "ssn2"

could I do

select *, '.' as ssn, '.' as ssn2

or woudl that give me the ambiguous column error deal (sorry I know I should have just tried it but I dont have a connection to the SQL server yet (they're working on it)
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>or would that give me the ambiguous column error deal
yes, that's what you would get.
0
 
sdstuberCommented:
some tools may help you build your query too.  Toad for MySQL should let you drag the columns you want from a browser into a query.
0
 
MMDeveloperAuthor Commented:
I was afraid of this, thanks you two.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.