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 
  1. CREATE TABLE [dbo].[User](  
  2.     [ID] [int] IDENTITY(1,1) NOT NULL,  
  3.     [FirstName] [varchar](50) NOT NULL,  
  4.     [LastName] [varchar](50) NULL,  
  5.     [UserName] [varchar](50) NOT NULL,  
  6.     [Email] [varchar](100) NULL,  
  7.     [Password] [varchar](100) NOT NULL,  
  8.     [CreatedOn] [datetime] NULL DEFAULT (GETDATE()),  
  9.     [UpdatedOn] [datetime] NULL,  
  10.     [IsActive] [bitNULL DEFAULT ((1)))  
I want to select all the columns except IsActive.
 
SQL Query 
  1. DECLARE @Temp NVARCHAR(MAX)  
  2. DECLARE @SQL NVARCHAR(MAX)  
  3.   
  4. SET @Temp = ''  
  5. SELECT @Temp = @Temp + COLUMN_NAME + ', '  
  6. FROM INFORMATION_SCHEMA.COLUMNS  
  7. WHERE TABLE_NAME ='User'   
  8. AND COLUMN_NAME NOT IN ('IsActive')  
  9.   
  10. SET @SQL = 'SELECT ' + SUBSTRING(@Temp, 0, LEN(@Temp)) +' FROM [User]'  
  11. 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.