Exclude a column using SELECT * [except columnA] FROM tableA?

Right click table in Object Explorer, Select top 1000 rows

It'll list all columns and not *. Then remove the unwanted column(s). Should be much faster than typing it yourself.

Then when you feel this is a bit too much work, get Red Gate's SQL Prompt, and type ssf<tab> from tbl, go to the * and click tab again.

Wouldn't it be simpler to do this:

sp_help <table_name>

-Click on the 'Column_name' column> Copy> Paste (creates a vertical list) into a New Query window and just type commas in front of each column value... comment out the columns you don't want... far less typing than any code offered here and still manageable.

A colleage advised a good alternative:

- Do SELECT INTO in your preceding query (where you generate or get the
data from) into a table (which you will delete when done). This will
create the structure for you.
- Do a script as CREATE to new query
window.
- Remove the unwanted columns. Format the remaining columns
into a 1 liner and paste as your column list.
- Delete the table you
created.

Done...


This helped us a lot.

No.

Maintenance-light best practice is to specify only the required columns.

At least 2 reasons:

- This makes your contract between client and database stable. Same data, every time
- Performance, covering indexes

Edit (July 2011):

If you drag from Object Explorer the `Columns` node for a table, it puts a CSV list of columns in the Query Window for you which achieves one of your goals

You can get SQL Complete from devart.com, which not just expands the * wildcard just like SQL Prompt from Red Gate does (as described in cairnz's answer), but also provides a column picker drop down with checkboxes in which you can check all the columns that you want in the select list and they will be inserted automatically for you (and if you then uncheck a column it will be automatically removed from the select list).

If you don't want to write each column name manually you can use `Script Table As` by right clicking on _table_ or _view_ in _SSMS_ like this:

![enter image description here][1]

Then you will get whole select query in _New Query Editor Window_ then remove unwanted column like this:

![enter image description here][2]

**Done**


[1]:

[2]:

If you're using mysql-workbench, you can right click on the table explorer and click on "Send to SQL editor->Select all statement".

It sends a statement like "Select col1, col2,... from tablename".

Then remove those that you don't need.

In SSMS there is an easier way with **IntelliSense** and **Aliasing**. Try this<ol>
<li> Right-Click in the text editor and make sure **IntelliSense** is enabled.
<li> Type the query with an alias [SELECT **t.*** FROM tablename t].
<li> Go the text **t.*** and delete the ***** ,and SSMS will auto-list the columns of the **f** aliased table.
</ol>
You can then quickly specify **only the columns you want** w/o having to use SSMS to write a select to another script and then do more copy/paste operations.
I use this all the time.

This won't save time on loading from the database. But, you could always unset the column you don't want in the array it's placed in. I had several columns in a table but didn't want one particular. I was too lazy to write them all out in the SELECT statement.


$i=0;
$row_array = array();

while($row = mysqli_fetch_assoc($result)){

$row_array[$i]=$row;
unset($row_array[$i]['col_name']);
$i++;
}

That what I use often for this case:

declare @colnames varchar(max)=''

select @colnames=@colnames+','+name from syscolumns where object_id(tablename)=id and name not in (column3,column4)

SET @colnames=RIGHT(@colnames,LEN(@colnames)-1)

`@colnames` looks like `column1,column2,column5`