All columns except selected 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
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.