如何在sql中选择除一列以外的所有列?

问题描述：

可能重复:
SQL使用SELECT *排除一列[[columnA除外] FROM tableA?

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

是否可能选择除一列之外的所有列?

Is selecting all columns except one column possible??

这是所有列名称:ID，名称，地址，年龄

here is all column names: id, name, address, age

SELECT id, name, address from TBLUser

我不想使用此select语句，因为我的表的列数彼此不同.

I don't want to use this select statement because the number of columns of my tables are different to each other.

答

declare @cols varchar(max), @sql varchar(max)
SELECT  @cols = STUFF
    (
        ( 
            SELECT DISTINCT '], [' + name
            FROM sys.columns
            where object_id = (
                select top 1 object_id from sys.objects
                where name = 'TBLUser'
            )
            and name not in ('age')
            FOR XML PATH('')
        ), 1, 2, ''
    ) + ']'
select @sql = 'select ' + @cols + ' from TBLUser'  
exec (@sql)

如何在sql中选择除一列以外的所有列?

相关推荐