Hi.

Is there a way to select all fields in a table but exclude one or more?

For example if I want to get all fields in a table called Names except the
BirthDate field a query might be:

SELECT FirstName, LastName, Height, Weight, FavoriteColor
FROM Names

Is there any easier way without having to type in every single field name
except those I don't want to see?

Something like:

SELECT INVERSE BirthDate
FROM Names

This would return all fields in the table except those listed. This is a
simple example with only a few fields but with large tables consisting of
many fields where I want to see all but a few it would be quicker to specify
those I don't want to see rather than all the ones I do want.

Thanks.
Kevin.

No.
And you should always specify every column in your select list anyway.  You
should also reference the column names in your code, rather  than relying on
position.  It makes sure that you know what you are retrieving, and more
importantly, the next person who accesses the code knows exactly what you
are doing.

I don't think I have ever seen a good reason for not listing your columns in
your select.

Show quoteHide quote

On Fri, 7 Apr 2006 14:34:58 -0400, "Jim Underwood"
<james.underwoodATfallonclinic.com> wrote:

I consider * to be a very valuable tool in the SELECT list, and prefer
it in many situations.  In general I prefer * when the query MUST
include EVERY column from a table.  It avoids the possible error of
leaving a column out, and it enforces a uniform sequence to the
columns that can't hurt.

Example: A view that has to include every column from a table, plus
other columns:
SELECT X.*, Y.SomeCol
If table X changes, all the is needed is to ALTER the view (with no
changes) to force a recompile.

When there are two tables with identical layouts and rows are inserted
from one into the other:
INSERT X
SELECT * FROM Y
It is very hard to get that wrong.  Again, if the tables change all
that is required is a recompile, removing one more chance to make an
error.  If only one of the tables change the recompile will fail,
which is a Good Thing as the issue of whether to change the other
table will be addressed.

I take the same approach to INSERT.  I do not list the columns to be
INSERTed unless required because only some are to be supplied.  If I
always used an explicit column list I would always have to be checking
to see if any columns are being skipped - VERY important information.
With my approach I know at once if there is a column list that
something is being skipped.  Not specifying the list also means that
the order of the INSERT list does not have to be checked, and can not
be changed by mistake.  Note that if there are many columns to the
INSERT I will generally not use a VALUES clause, but a SELECT (even if
the data does not come from a table).  I wrote a little proc to
generate a SELECT from a single table with every column provided in
order.  I run that proc, specifying the target table name, to get a
SELECT that matches the implicit column list of the INSERT, then edit
that to supply whatever values the INSERT requires.

Roy Harvey
Beacon Falls, CT

If you right click on a table name in the tree in Analyzer, it will generate
a select statement for you based on the table, then you only have to erase a
couple.

Show quoteHide quote

Trigger logic question - using If with AND and OR
Advanced T-SQL Question: Produce Backup Window Gantt Chart
SQL/CLR books
Performance question
Unexpected NOT IN results
Invalid Column Names?
Why am I missing rows in my result set?
[OT] There should be a law...
how to get back image from table?
HELP WITH TRIGGER