How to select all columns in sql except one column?

Question

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

Is selecting all columns except one column possible??

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

SELECT id, name, address from TBLUser

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

the query you've shown is one alternative. the other one, *which gives a little pain in the neck*, is to create a Dynamic SQL. — John Woo, Jan 18 '13 at 03:56
[Here's](http://stackoverflow.com/a/1712243/679449) a solution. — Kermit, Jan 18 '13 at 03:59
@JW. - can you give me some example of dynamic sql because i have no idea of that — , Jan 18 '13 at 04:02

score 5 · Accepted Answer · answered Jan 18 '13 at 04:28

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)

Michael Eakins · Answer 2 · 2019-04-18T12:45:19.970

How about:

SELECT * FROM sys.columns 
WHERE Name <> N'Column To Exclude' and Object_ID = Object_ID(N'TBLUser')

This will return all columns except the one you wish to exclude.

Expanded explanation:

According to this explanation from SQL Cheat Sheet

sys.columns is a system table and is used for maintaining information on columns in a database. For every column added in database, a record is created in the sys.columns table. There is only one record for each column

Name: The name of the column. This is unique within the table object.

Object_id:object_id is unique identifier for table in which the column exists. We will use this column to join sys.columns with sys.tables in order to fetch columns in different tables.

We are selecting all results from sys.columns where the name is not equal to whatever you provide, replace 'Column To Exclude' with your column name. We are also requiring that the object_id equal the object_id you provide. object_id is a number that represents the table you want to filter out the one column from. In this ops case the table was TBLUser, in other uses it would be like this object_id(N'[dbo].[YourTable]'), you would replace [YourTable] with your own table name

@TheTerribleChild I have expanded my answer to further explain what we are doing here. — Michael Eakins, Apr 18 '19 at 12:45

score 1 · Answer 3 · answered Jan 18 '13 at 04:23

1

in sql*plus,

the one way is to disable as follows:

sql> column age noprint
sql> SELECT * from TBLUser

then, you can revert using

sql>column age off

or else you've to user dynamically with DBMS_SQL package.

answered Jan 18 '13 at 04:23

ajmalmhd04

2,582
6
23
41

5

SQL PLUS is for Oracle which is not mentioned by the OP – Michael Eakins Jan 18 '13 at 13:27

How to select all columns in sql except one column?

3 Answers3

Linked