2

is there a way to select all the columns but one specific I don't want to be selected? I mean sometimes I have that problem that table has hundreds of fields and I need to get rid of one only. Do I need to rewrite all columns? is there a trick to that? like select * -<column_name> from table ? thanks

Zulu Z
  • 1,103
  • 5
  • 14
  • 27
  • Create a view that has whatever columns you want and select * from view – Kiran Hegde Jul 03 '14 at 15:08
  • NO you need to sepcify columns, Of course you can drag them over from the object browser to dsave key strokes. Of course you shoudl be always specifying columsn in every query as select * should not be usedin production code. – HLGEM Jul 03 '14 at 15:09
  • possible repeat of http://stackoverflow.com/questions/729197/sql-exclude-a-column-using-select-except-columna-from-tablea – durbnpoisn Jul 03 '14 at 15:09
  • I do lke Kiran's sugestion though. – durbnpoisn Jul 03 '14 at 15:10
  • sorry, this question indeed been answered already - so there is no way besides tricks and walk around. thanks – Zulu Z Jul 03 '14 at 19:10

2 Answers2

2

This is a bit long for a comment.

You need to write all the columns or use dynamic SQL. However, getting all the columns into the query is not necessarily that hard. You can query the metadata (such as INFORMATION_SCHEMA.COLUMNS), choosing the appropriate columns for your query.

In SQL Server Management Studio, you can browse to the table. Under the table name is the "Columns" header. Then just drag "Columns" into the query pane. Voila! All the columns in the table are inserted. You can manually delete the one you want to delete.

Code Maverick
  • 20,171
  • 12
  • 62
  • 114
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
1

I'd suggest using a third party intellisense provider. Many are free and will add the list of columns for you, then you can just remove the one you don't want. I use SQL Prompt from Red Gate, which isn't free, but saves me tons of time, and so it's worth the money.

Dave.Gugg
  • 6,561
  • 3
  • 24
  • 43