All columns except selected

Question

Is it possible to select all fields except few fields for a single table? (Suppose there are bundle of fields that writing each field in Select statement is time consuming).

id  row_num  customer_code comments
-----------------------------------
1   1        Dilbert        Hard
1   2        Dilbert        Worker
2   1        Wally          Lazy

Something like:

Select * Except (row_num) from Table

The above syntax is wrong because I get an error "select * must have a from clause"

So what is the appropriate syntax to get an output:

id  customer_code comments
-----------------------------------
1   Dilbert        Hard
1   Dilbert        Worker
2   Wally          Lazy

It is impossible. You can, of course, to create a procedure which obtains fieldslist from INFORMATION_SCHEMA, builds proper SQL core and executes it as prepared statement - but I think it is excess extremely. Do not be so lazy... — Akina, Mar 27 '19 at 04:45
Rather than writing your query with exclusions, can you not simply script out the SELECT statement in SSMS and then remove the columns you don't want? Right-click the table in Object Explorer and click Select Top 1000 Rows. This will generate a script and you can remove the columns you don't want. — HandyD, Mar 27 '19 at 04:45
I have 50 fields that's why. Not being lazy. Why is it impossible? What is the procedure that you are talking about? Can you share the link or a document I can refer to? Thanks — user175623, Mar 27 '19 at 04:46
@HandyD one reason is because I am trying to find and learn a useful query — user175623, Mar 27 '19 at 04:47
The INFORMATION_SCHEMA COLUMNS Table, CREATE PROCEDURE and CREATE FUNCTION Syntax, Prepared SQL Statement Syntax. — Akina, Mar 27 '19 at 04:48
Possible solutions already provided here potentially: https://stackoverflow.com/questions/729197/sql-exclude-a-column-using-select-except-columna-from-tablea . . . Sometimes you don't want to try to be efficient and rather be explicit even if that means more typing, welcome to DBA work. Repetition makes perfect and plus the more you type, the faster you get. — IT Thug Ninja, Mar 27 '19 at 04:51
Which DBMS product are you using? "SQL" is just a query language, not the name of a specific database product. Please add a tag for the database product you are using postgresql, oracle, sql-server, db2, ... — , Mar 27 '19 at 06:55

score 0 · Accepted Answer · answered Mar 27 '19 at 05:15

You could create a stored procedure and use Dynamic SQL and the INFORMATION_SCHEMA tables to generate a SELECT statement dynamically.

Stored Procedure:

CREATE PROCEDURE spGenerateSelect (@SchemaName NVARCHAR(255), 
    @TableName NVARCHAR(255),
    @ExcludeColumns NVARCHAR(MAX)
)
AS
BEGIN

    SELECT @ExcludeColumns = '''' + REPLACE(@ExcludeColumns, ', ', ''', ''') + ''''

    DECLARE @SqlCmd NVARCHAR(MAX),
        @ParamDefinition NVARCHAR(MAX),
        @ColumnsOut NVARCHAR(MAX)

    SET @ParamDefinition = '@SchemaName NVARCHAR(255), @TableName NVARCHAR(255), @Columns NVARCHAR(MAX) OUTPUT'

    SET @SqlCmd = 'SELECT @Columns = COALESCE(@Columns + '', '', '''') + QUOTENAME(c.COLUMN_NAME)
    FROM INFORMATION_SCHEMA.COLUMNS c
    INNER JOIN INFORMATION_SCHEMA.TABLES t ON t.TABLE_SCHEMA = c.TABLE_SCHEMA AND t.TABLE_NAME = c.TABLE_NAME
    WHERE t.TABLE_SCHEMA = @SchemaName AND t.TABLE_NAME = @TableName AND c.COLUMN_NAME NOT IN (' + @ExcludeColumns + ');'

    EXEC sp_executesql @SqlCmd, @ParamDefinition, @SchemaName = @SchemaName, @TableName = @TableName, @Columns = @ColumnsOut OUTPUT

    SELECT 'SELECT ' + @ColumnsOut + ' FROM ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName)
END

Execution:

EXEC spGenerateSelect @SchemaName = 'dbo', @TableName = 'SubjectPeriod', @ExcludeColumns = 'SubjectID, TeacherID'

This outputs a SELECT statement for all columns, excluding the ones supplied in @ExcludeColumns, which is a comma-separated list of column names to exclude.

Thank you so much for the statement! Would love to vote for your answer but don't have enough reputation so the system doesn't allow me. Sorry for that — user175623, Mar 27 '19 at 23:16
Just an additional note, I used INFORMATION_SCHEMA specifically to exclude many system objects automatically, so this won't work for tables in the sys schema. If you want system objects you need to refactor the query in the proc to use sys.tables, sys.columns and sys.schemas instead of the current tables in use. — HandyD, Mar 28 '19 at 00:10
Getting more complicated (sigh), but thanks for the tips. That makes a big difference.Refactoring it. Thanks — user175623, Mar 28 '19 at 00:27

All columns except selected

1 Answers1