SELECT (* - some_columns) FROM TABLE in SQL

Question

Hi,

I have a table with many columns among which I'd like to omit a few alone in my 'select' query. Something like select (* - columns_to_be_omitted) from myTable. Is there a way to do this, other than listing all the other columns in the query?

This is a one-time manual query, so I'm not that concerned about performance. Ease of use is the greater need here.

Answer 1

You can't do this in straight SQL. The best you can do is write a stored procedure (anonymous or otherwise) that constructs a dynamic SQL statement and execute that.

You can include all or include some but you can't include all and exclude some (barring weird vendor extensions that may exist with different databases). There's no such thing in MySQL.

Answer 2

I to this day cannot understand how M$ could ship an IDE and a management studio that doesn't have intellisense... I have now been corrected that 2008 has this, 2005 does not.

If you have SQL Management Studio, then you can use the script select to new query window which will give you a template select statement which you can delete from. Just right click on the table.

Good luck :)

Answer 3

You can't do that - the best you can do is use a tool like SQL Prompt if you're using SQL Server Management Studio.

SQL Prompt will allow you to do a SELECT * FROM MyTable and if you put your cursor after the "*" and press tab, SQL Prompt will enumerate all columns for your table, giving you a list of all columns in the table from which you can then remove those you're not interested in. Or you can open up a popup dialog box which allows you to pick any number of columns from that table which are then inserted into the SELECT statement.

Too bad that's not part of SSMS out of the box :-(

Marc

Answer 4

There is no direct way to do. You can mention the invididual columns which you want in select query otherwise you can create a temprory table by giving selective column from your table and use that temporary table.

Answer 5

You can dynamically constuct a list of columns using a query against the data dictionary. Then you could add your list of unwanted columns in a where-clause (like column-name not in ()). (Not sure how you could do that with the stored proc approach that cletus suggested.)