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

Question

You can try it this way:

/* Get the data into a temp table */
SELECT * INTO #TempTable
FROM YourTable
/* Drop the columns that are not needed */
ALTER TABLE #TempTable
DROP COLUMN ColumnToDrop
/* Get results and drop temp table */
SELECT * FROM #TempTable
DROP TABLE #TempTable

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

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:

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

Done

The automated way to do this in SQL (SQL Server) is:

declare @cols varchar(max), @query varchar(max);
SELECT  @cols = STUFF
    (
        ( 
            SELECT DISTINCT '], [' + name
            FROM sys.columns
            where object_id = (
                select top 1 object_id from sys.objects
                where name = 'MyTable'
            )
            and name not in ('ColumnIDontWant1', 'ColumnIDontWant2')
            FOR XML PATH('')
        ), 1, 2, ''
    ) + ']';

SELECT @query = 'select ' + @cols + ' from MyTable';  
EXEC (@query);

You could create a view that has the columns you wish to select, then you can just select * from the view...

Yes it's possible (but not recommended).

CREATE TABLE contact (contactid int, name varchar(100), dob datetime)
INSERT INTO contact SELECT 1, 'Joe', '1974-01-01'

DECLARE @columns varchar(8000)

SELECT @columns = ISNULL(@columns + ', ','') + QUOTENAME(column_name)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'contact' AND COLUMN_NAME <> 'dob'
ORDER BY ORDINAL_POSITION

EXEC ('SELECT ' + @columns + ' FROM contact')

Explanation of the code:

1. Declare a variable to store a comma separated list of column names. This defaults to NULL.
2. Use a system view to determine the names of the columns in our table.
3. Use SELECT @variable = @variable + ... FROM to concatenate the column names. This type of SELECT does not not return a result set. This is perhaps undocumented behaviour but works in every version of SQL Server. As an alternative you could use SET @variable = (SELECT ... FOR XML PATH('')) to concatenate strings.
4. Use the ISNULL function to prepend a comma only if this is not the first column name. Use the QUOTENAME function to support spaces and punctuation in column names.
5. Use the WHERE clause to hide columns we don't want to see.
6. Use EXEC (@variable), also known as dynamic SQL, to resolve the column names at runtime. This is needed because we don't know the column names at compile time.