Select All Columns From A Table Except One Column

Introduction

Usually, we use * to select all the columns but if we have to select all the columns except one column, then we have to write all the required columns one by one. Here, I am going to show how we can achieve it without writing all the columns one by one.

 

Create a sample table 

CREATE TABLE [dbo].[User](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [FirstName] [varchar](50) NOT NULL,
    [LastName] [varchar](50) NULL,
    [UserName] [varchar](50) NOT NULL,
    [Email] [varchar](100) NULL,
    [Password] [varchar](100) NOT NULL,
    [CreatedOn] [datetime] NULL DEFAULT (GETDATE()),
    [UpdatedOn] [datetime] NULL,
    [IsActive] [bit] NULL DEFAULT ((1)))

I want to select all the columns except IsActive.

 

SQL Query 

DECLARE @Temp NVARCHAR(MAX)
DECLARE @SQL NVARCHAR(MAX)

SET @Temp = ''
SELECT @Temp = @Temp + COLUMN_NAME + ', '
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME ='User'
AND COLUMN_NAME NOT IN ('IsActive')

SET @SQL = 'SELECT ' + SUBSTRING(@Temp, 0, LEN(@Temp)) +' FROM [User]'
EXECUTE SP_EXECUTESQL @SQL

Output

 

 

Conclusion

 

If you have to select all the columns except more than one column, let's say for instance two columns, then pass your column name separated by comma in a COLUMN_NAME NOT IN clause.

I still recommend writing columns explicitly if you have such a requirement.