How to select all columns in sql except one column? [duplicate] How to select all columns in sql except one column? [duplicate] sql sql

How to select all columns in sql except one column? [duplicate]

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)

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

in sql*plus,

the one way is to disable as follows:

sql> column age noprintsql> SELECT * from TBLUser

then, you can revert using

sql>column age off

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