选择 * 除 [英] SELECT * EXCEPT

问题描述

是否有任何 RDBMS 实现了类似 SELECT * EXCEPT 的功能?我所追求的是获取除特定 TEXT/BLOB 字段之外的所有字段，我只想选择其他所有字段.

Is there any RDBMS that implements something like SELECT * EXCEPT? What I'm after is getting all of the fields except a specific TEXT/BLOB field, and I'd like to just select everything else.

我几乎每天都向我的同事抱怨应该有人实施这个......它不存在真是太烦人了.

Almost daily I complain to my coworkers that someone should implement this... It's terribly annoying that it doesn't exist.

我理解每个人都对 SELECT * 的担忧.我知道与 SELECT * 相关的风险.但是，至少在我的情况下，这不会用于任何生产级别的代码，甚至开发级别的代码；严格用于调试，当我需要轻松查看所有值时.

I understand everyone's concern for SELECT *. I know the risks associated with SELECT *. However, this, at least in my situation, would not be used for any Production level code, or even Development level code; strictly for debugging, when I need to see all of the values easily.

正如我在一些评论中所说的，我工作的地方严格来说是一个命令行商店，一切都通过 ssh 完成.这使得很难使用任何 gui 工具(不允许外部连接到数据库)等.

As I've stated in some of the comments, where I work is strictly a commandline shop, doing everything over ssh. This makes it difficult to use any gui tools (external connections to the database aren't allowed), etc etc.

谢谢你的建议.

推荐答案

正如其他人所说，在查询中执行此操作不是一个好主意，因为将来有人更改表结构时很容易出现问题.但是，有一种方法可以做到这一点......我不敢相信我实际上是在建议这个，但本着回答实际问题的精神......

As others have said, it is not a good idea to do this in a query because it is prone to issues when someone changes the table structure in the future. However, there is a way to do this... and I can't believe I'm actually suggesting this, but in the spirit of answering the ACTUAL question...

使用动态 SQL 执行此操作...这会执行除描述"列之外的所有列.您可以轻松地将其转换为函数或存储过程.

Do it with dynamic SQL... this does all the columns except the "description" column. You could easily turn this into a function or stored proc.

declare @sql varchar(8000),
    @table_id int,
    @col_id int

set @sql = 'select '

select @table_id = id from sysobjects where name = 'MY_Table'

select @col_id = min(colid) from syscolumns where id = @table_id and name <> 'description'
while (@col_id is not null) begin
    select @sql = @sql + name from syscolumns where id = @table_id and colid = @col_id

    select @col_id = min(colid) from syscolumns where id = @table_id and colid > @col_id and name <> 'description'
    if (@col_id is not null) set @sql = @sql + ','
    print @sql
end

set @sql = @sql + ' from MY_table'

exec @sql

这篇关于选择 * 除的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！