SQL 使用 SELECT * [except columnA] FROM tableA 排除列？

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

提问by

We all know that to select all columns from a table, we can use

我们都知道要从表中选择所有列，我们可以使用

SELECT * FROM tableA

Is there a way to exclude column(s) from a table without specifying all the columns?

有没有办法在不指定所有列的情况下从表中排除列？

SELECT * [except columnA] FROM tableA

The only way that I know is to manually specify all the columns and exclude the unwanted column. This is really time consuming so I'm looking for ways to save time and effort on this, as well as future maintenance should the table has more/less columns.

我知道的唯一方法是手动指定所有列并排除不需要的列。这真的很耗时，所以我正在寻找节省时间和精力的方法，以及如果表有更多/更少列的未来维护。

回答by Norman Skinner

I agree with everyone... but if I was going to do something like this I might do 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

回答by gbn

No.

不。

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

维护轻的最佳实践是仅指定所需的列。

At least 2 reasons:

至少有2个原因：

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

• 这使您的客户端和数据库之间的合同稳定。每次都一样的数据
• 性能，覆盖指标

Edit (July 2011):

编辑（2011 年 7 月）：

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

如果您从对象资源管理器中拖动Columns表的节点，它会在查询窗口中为您放置一个列的 CSV 列表，从而实现您的目标之一

回答by pl80

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

在 SQL (SQL Server) 中执行此操作的自动化方法是：

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

回答by hims056

If you don't want to write each column name manually you can use Script Table Asby right clicking on tableor viewin SSMSlike this:

如果您不想手动编写每个列名，可以Script Table As通过右键单击表或在SSMS 中查看来使用，如下所示：

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

然后您将在新查询编辑器窗口中获得整个选择查询，然后删除不需要的列，如下所示：

Done

完毕

回答by campo

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

您可以创建一个包含您希望选择的列的视图，然后您就可以select *从视图中...

回答by Anthony Faull

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 + ... FROMto concatenate the column names. This type of SELECTdoes 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 ISNULLfunction to prepend a comma only if this is not the first column name. Use the QUOTENAMEfunction to support spaces and punctuation in column names.
5. Use the WHEREclause 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.

1. 声明一个变量来存储逗号分隔的列名列表。这默认为 NULL。
2. 使用系统视图来确定表中列的名称。
3. 使用SELECT @variable = @variable + ... FROM来连接的列名。这种类型SELECT不返回结果集。这可能是未记录的行为，但适用于 SQL Server 的每个版本。作为替代方案，您可以使用SET @variable = (SELECT ... FOR XML PATH(''))连接字符串。
4. ISNULL仅当这不是第一个列名时，才使用该函数在前面添加逗号。使用该QUOTENAME函数支持列名称中的空格和标点符号。
5. 使用WHERE子句隐藏我们不想看到的列。
6. 使用EXEC (@variable)，也称为动态 SQL，在运行时解析列名。这是必需的，因为我们在编译时不知道列名。

回答by MrTelly

Like the others have said there is no way to do this, but if you're using Sql Server a trick that I use is to change the output to comma separated, then do

就像其他人所说的那样，没有办法做到这一点，但是如果您使用的是 Sql Server，我使用的一个技巧是将输出更改为逗号分隔，然后执行

select top 1 * from table

and cut the whole list of columns from the output window. Then you can choose which columns you want without having to type them all in.

并从输出窗口中剪切整个列列表。然后，您可以选择所需的列，而无需全部输入。

回答by marc_s

Basically, you cannot do what you would like - but you can get the right tools to help you out making things a bit easier.

基本上，你不能做你想做的事——但你可以得到正确的工具来帮助你让事情变得更容易。

If you look at Red-Gate's SQL Prompt, you can type "SELECT * FROM MyTable", and then move the cursor back after the "*", and hit <TAB> to expand the list of fields, and remove those few fields you don't need.

如果您查看 Red-Gate 的SQL Prompt，您可以键入“SELECT * FROM MyTable”，然后将光标移回“*”之后，然后点击 <TAB> 以展开字段列表，并删除您选择的那几个字段不需要。

It's not a perfect solution - but a darn good one! :-) Too bad MS SQL Server Management Studio's Intellisense still isn't intelligent enough to offer this feature.......

这不是一个完美的解决方案 - 而是一个非常好的解决方案！:-) 太糟糕了 MS SQL Server Management Studio 的 Intellisense 仍然不够智能，无法提供此功能......

Marc

马克

回答by Ali Kazmi

no there is no way to do this. maybe you can create custom views if that's feasible in your situation

不，没有办法做到这一点。如果在您的情况下可行，也许您可​​以创建自定义视图

EDITMay be if your DB supports execution of dynamic sql u could write an SP and pass the columns u don't want to see to it and let it create the query dynamically and return the result to you. I think this is doable in SQL Server atleast

编辑可能是如果您的数据库支持动态 sql 的执行，您可以编写一个 SP 并将您不想看到的列传递给它，让它动态创建查询并将结果返回给您。我认为这至少在 SQL Server 中是可行的

回答by asdasdasd

If you are using SQL Server Management Studio then do as follows:

如果您使用的是 SQL Server Management Studio，请执行以下操作：

1. Type in your desired tables name and select it
2. Press Alt+F1
3. o/p shows the columns in table.
4. Select the desired columns
5. Copy & paste those in your select query
6. Fire the query.

1. 输入您想要的表名称并选择它
2. 按Alt+F1
3. o/p 显示表中的列。
4. 选择所需的列
5. 复制并粘贴到您选择的查询中
6. 触发查询。

Enjoy.

享受。