DECLARE @Columns NVARCHAR(MAX)='',@SQL NVARCHAR(MAX)
SELECT @Columns=CASE WHEN @Columns='' THEN name ELSE @Columns+','+name END  
FROM sys.columns 
WHERE object_ID=(sELECT id FROM sysobjects WHERE name='TheTableName') and NAME!='WithoutColumnName'

SELECT @SQL='SELECT '+@Columns+' FROM dbo.TheTableName'
EXEC sp_execute @SQL

我这样做了，它工作得很好(版本5.5.41):

# prepare column list using info from a table of choice
SET @dyn_colums = (SELECT REPLACE(
GROUP_CONCAT(`COLUMN_NAME`), ',column_name_to_remove','') 
FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE 
`TABLE_SCHEMA`='database_name' AND `TABLE_NAME`='table_name');

# set sql command using prepared columns
SET @sql = CONCAT("SELECT ", @dyn_colums, " FROM table_name");

# prepare and execute
PREPARE statement FROM @sql;
EXECUTE statement;

您可以使用REGEX列规范。

下面的查询选择除ds和hr之外的所有列 选择(ds |人力资源)? +。+来自销售

在SQL Management Studio中，您可以展开对象资源管理器中的列，然后将columns树项拖到查询窗口中，以获得以逗号分隔的列列表。

是的，这是可能的(但不建议)。

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')

代码说明:

Declare a variable to store a comma separated list of column names. This defaults to NULL. Use a system view to determine the names of the columns in our table. 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. 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. Use the WHERE clause to hide columns we don't want to see. 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.

下面是生成在查询中使用的列的列表(不是自动查询，因为这没有指定):

SELECT 
    GROUP_CONCAT(
        CONCAT('`', `COLUMN_NAME`, '`')
        SEPARATOR ',\n'
    ) AS `cols`
FROM information_schema.`COLUMNS`
WHERE `TABLE_SCHEMA` = 'db'
AND `TABLE_NAME` = 'table_name_here'
AND `COLUMN_NAME` NOT IN ('exclude_col1', 'exclude_col2')

会产生:

`included_col1`,
`included_col2`,
`included_col3`

然后你可以复制这个在查询中使用:

SELECT
    `included_col1`,
    `included_col2`,
    `included_col3`
FROM db.table_name_here