exclude one column

Question

exclude one column

tripri

SSCommitted

Points: 1584
More actions
June 9, 2010 at 1:30 pm

#233624

hi,
i got a table with almost 20 columns. Now the problem is
if i do
Select * from table
i will get all the wenty columns
what if i want only 19 columns excluding one column

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Sioban Krzywicki One Orange Chip Points: 27770 More actions · Answer 1

I would suggest explicity stating each column you want to return in the query even if you want to return all 20. Using * is sloppy and can give you unintended results, especially if the underlying tables change and the query doesn't.

--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams

Steve Cullen SSCertifiable Points: 5598 More actions · Answer 2

It is considered poor practice to use "select *" for anything but simple, temporary queries.

The good new is that it's easy to make SQL write your queries for you. Simply right-click in the object explorer on the table and select "script table as select to new query editor window". A complete query is written for you. You can then remove the unwanted column or columns.

Converting oxygen into carbon dioxide, since 1955.

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 3

By stating SELECT *, you're asking for everything. The only way to not get everything is to explicitly state what you want as the others have already said.

But, you can look at setting up a view that defines the 19 columns that you want. Then, a SELECT * from the view will only return 19 columns.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Hari.Sharma SSCertifiable Points: 6904 More actions · Answer 4

Steve Cullen (6/9/2010)
It is considered poor practice to use "select *" for anything but simple, temporary queries.
The good new is that it's easy to make SQL write your queries for you. Simply right-click in the object explorer on the table and select "script table as select to new query editor window". A complete query is written for you. You can then remove the unwanted column or columns.

I agree with Steve. We should not use "SELECT *". If you have large number of columns, Steve already explained how to generate a query...

Cheers,
Hari
Tips & Tricks for SQL BI Developers

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 5

tripri (6/9/2010)
hi,
i got a table with almost 20 columns. Now the problem is
if i do
Select * from table
i will get all the wenty columns
what if i want only 19 columns excluding one column

As already pointed out, the use of SELECT * is generally a bad thing to do. Usually, selecting 19 out of 20 columns from a table is equally as bad. However, it's your sword. If you need to do such a thing more than once, I suggest you make a view to include only the necessary 19 columns so you can do your SELECT *.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 6

Heh... drat... day late and a dollar short. I just saw what Grant already posted.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Paul White SSC Guru Points: 150467 More actions · Answer 7

Another way is to use dynamic SQL (for example if the excluded column is determined by a computation).

Example:

CREATE TABLE dbo.Test

(

A INT NULL,

B INT NULL,

C INT NULL,

D INT NULL,

E INT NULL,

F INT NULL

);

INSERT dbo.Test (A, B, C, D, E, F) VALUES (1, 2, 3, 4, 5, 6);

DECLARE @ColumnNames NVARCHAR(MAX);

SELECT @ColumnNames =

STUFF(

(

-- All columns from the table

SELECT N',' + name

FROM sys.columns

WHERE [object_id] = OBJECT_ID(N'dbo.Test', N'U')

AND name <> N'C' -- except this one

ORDER BY column_id

FOR XML PATH(''), TYPE

).value('./text()[1]', 'NVARCHAR(MAX)')

, 1, 1, SPACE(0));

EXECUTE (N'SELECT ' + @ColumnNames + N' FROM dbo.Test;');

GO

DROP TABLE dbo.Test;

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi