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.