How to select all columns except the specified columns in SQL

Question

I have followed logic provided by Anthony Faull from SQL exclude a column using SELECT * [except columnA] FROM tableA?

Here is the code I have wrote

if OBJECT_ID('tempdb..#ReportRows') is not null
drop table #ReportRows

Select * 
into #ReportRows
from
(Select 
Alpha,Beta,Gamma,XMan,Pathwaycode,STDCode,JiraCode
from 'Table1'
)
order by alpha

DECLARE @columns varchar(8000)

SELECT @columns = ISNULL(@columns + ', ','') + QUOTENAME(column_name)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = '#ReportRows' AND COLUMN_NAME not in ('[PathwayCode]','[Gamma]')
ORDER BY ORDINAL_POSITION

EXEC ('SELECT ' + @columns + 'FROM #ReportRows')

Everytime I ran this I get the following error "Msg 156, Level 15, State 1, Line 3 Incorrect syntax near the keyword 'FROM'."

Just `PRINT'SELECT ' + @columns + 'FROM #ReportRows'` I guess the last comma `,` is causing problems. Anyway `SELECT @col = @col + ... ` is undefined behaviour. — Lukasz Szozda, Feb 14 '18 at 16:14
You link to a question where all the answers say *don't do this*. Well, *don't* do this. It's extremely easy to select the query in SSMS, type `Ctrl+Shift+Q` to bring up the designer and uncheck any column you don't want — Panagiotis Kanavos, Feb 14 '18 at 16:14
Wouldn't you be better doing it as a 1 time action of creating a view and just select from that? — Andrew, Feb 14 '18 at 16:14
You have a subquery in your first SELECT statement, but it lacks an alias. (for one) — JNevill, Feb 14 '18 at 16:16
@PanagiotisKanavos it doesn't show any temporary tables, only the existing tables and I only have read access — Ashikur Rahman, Feb 14 '18 at 16:22
@JNevill if you don't use alias it doesn't matter because it's selecting from whatever is in the bracket. — Ashikur Rahman, Feb 14 '18 at 16:23
You got a syntax error in a dynamic SQL statement and you didn't PRINT the dynamic query to see what it was trying to execute? That should have been the first thing you thought of, way before you thought of posting a question. — Tab Alleman, Feb 14 '18 at 16:26
@AshikurRahman you didn't try it. Try it. Select your query, type `Ctrl+Shift+Q`. Simply by hitting Enter you'll get all the columns. You can uncheck the columns that you don't want — Panagiotis Kanavos, Feb 14 '18 at 16:26
@PanagiotisKanavos if I am selecting from say 3 different tables, and I type Select table1.* table2.* table3.*. It wouldn't show all the columns in query designer — Ashikur Rahman, Feb 14 '18 at 16:41
@AshikurRahman first, yes it will and does so since the 1990s. Wouldn't be much of a designer if it didn't. You probably refer to temporary tables that you *shouldn't* be using in a report query. Second, create a view to hide the complexity. You are asking about *columns*. Why would you want to have to deal with *tables* as well? — Panagiotis Kanavos, Feb 15 '18 at 08:24
@AshikurRahman assume that the people that tell you "don't do it" have to create and maintain *multiple* reports over multiple multi-million tables in data warehouses, for complex scenarios like reconciliation. You need a schema that makes reporting *easier*. Star schemas are great for this. Reporting tools, Excel *and* SSMS work great with such schemas. If you need to use temporary tables it means you are trying to replicate the ETL process in the query itself. You can't go very far this way. You can't *maintain* it this way. — Panagiotis Kanavos, Feb 15 '18 at 08:29

Thom A · Answer 1 · 2018-02-14T16:47:52.490

If you have to do this using Dynamic SQL, this should get you started:

DECLARE @SQL nvarchar(MAX);

SELECT @SQL = N'SELECT ' +
              STUFF(CONVERT(varchar(MAX),(SELECT N',' + NCHAR(10) + QUOTENAME(c.[name])
                                          FROM sys.columns c
                                               JOIN sys.tables t ON c.object_id = t.object_id
                                          WHERE c.[name] NOT IN (N'PathwayCode',N'Gamma')
                                          --Note I use STRING_SPLIT ehre, which is a 2016 Function
                                          --You'll need to find a String Splitter for prior versions
                                            AND t.[name] = N'YourTable'
                                          FOR XML PATH(N''), TYPE)),1,2,N'') + NCHAR(10) +
              N'FROM YourTable;';

PRINT @SQL;
EXEC sp_executesql @SQL;

Honestly, though, this seems more work than it solves; especially as we're only omitting 2 columns. SSMS can easily provide a SELECT statement with all the column in, and you can then easily remove those from the statement.

If you need a more permanent object, then use a VIEW.

Edit:

If you want to make this "truly" dynamic, you could do it this way:

CREATE PROC Select_Exclude @Table sysname, @Columns nvarchar(4000) AS

    DECLARE @SQL nvarchar(max);

    SELECT @SQL = N'SELECT ' +
                  STUFF(CONVERT(varchar(MAX),(SELECT N',' + NCHAR(10) + QUOTENAME(c.[name])
                                              FROM sys.columns c
                                                   JOIN sys.tables t ON c.object_id = t.object_id                                               
                                              WHERE c.[name] NOT IN (SELECT SS.[value] FROM STRING_SPLIT(@Columns,',') SS)
                                                AND t.[name] = N'YourTable'
                                              FOR XML PATH(N''), TYPE)),1,2,N'') + NCHAR(10) +
                  N'FROM ' + QUOTENAME(ot.[name]) + ';'
    FROM sys.tables ot
    WHERE ot.[name] = @Table;

    PRINT @SQL;
    EXEC sp_executesql @SQL;
GO

EXEC Select_Exclude @Table = N'YourTable', @Columns = N'PathwayCode,Gamma';

But again, why..? This is just over the top. I really did this more to prove a point.

I am replicating business reprot and it contains 325 columns, I am using 10 different temp table and creating a one big table at the end. I need to omit about 25 columns. Hence, I require this — Ashikur Rahman, Feb 14 '18 at 16:26
@AshikurRahman not really. Create a view to abstract the complex query. — Panagiotis Kanavos, Feb 14 '18 at 16:27
@AshikurRahman besides, reporting tools *can* help you to select columns for a simple query, eg over the view. On the other hand if you have so many temporary tables in the *report*, you need to seriously reconsider your schema. Reporting databases are meant to make reporting easier. Cleaning, staging etc should be done while *loading* the data, not in the report itself — Panagiotis Kanavos, Feb 14 '18 at 16:29
I have to agree with @PanagiotisKanavos and Lamu. This is way more work than it solves. Make the query the normal way and put in into a view. That way you can re-use it easy and maintain it easy — GuidoG, Feb 14 '18 at 16:32
Yes, as several others have mentioned, @AshikurRahman, this isn't really a maintainable solution. I *could* set it up to accept a dynamic table name as well, and a delimited string or datatable containing the column names to exclude, but my point remains; it's more to do that than it is to simply remove the columns you don't want in a pre-generated `SELECT` statement. — Thom A, Feb 14 '18 at 16:41
@AshikurRahman There's now a proc for you, if you a full dynamic SQL solution, but, I repeat, again: **Make a `VIEW`.** — Thom A, Feb 14 '18 at 16:48
@larnu in SQL Server 2017 one could use STRING_ARG. And it would still be a bad idea — Panagiotis Kanavos, Feb 15 '18 at 08:31

Pawan Kumar · Answer 2 · 2018-02-14T16:35:13.780

I have used your code and created a sample for you with tested output. Please try.

Generate Data.

USE [master]
GO
/****** Object:  Table [dbo].[FindRecordsWithALength]    Script Date: 2/14/2018 10:01:49 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[FindRecordsWithALength](
    [Id] [int] NULL,
    [Name] [varchar](10) NULL,
    [Length] [int] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[FindRecordsWithALength] ([Id], [Name], [Length]) VALUES (1, N'A', 5)
GO
INSERT [dbo].[FindRecordsWithALength] ([Id], [Name], [Length]) VALUES (2, N'B', 3)
GO
INSERT [dbo].[FindRecordsWithALength] ([Id], [Name], [Length]) VALUES (3, N'C', 4)
GO
INSERT [dbo].[FindRecordsWithALength] ([Id], [Name], [Length]) VALUES (4, N'D', 5)
GO

SOLUTION

IF OBJECT_ID('tempdb..#ReportRows') IS NOT NULL
DROP TABLE #ReportRows

SELECT * INTO #ReportRows from FindRecordsWithALength

DECLARE @columns VARCHAR(MAX) = ''
DECLARE @SQL VARCHAR(MAX) = ''

SELECT @columns = ISNULL(@columns + ',','') + QUOTENAME(a.NAME) 
FROM tempdb.sys.columns a 
INNER JOIN tempdb.sys.objects s ON a.object_id = s.object_id
WHERE s.NAME like  '#ReportRows%' AND s.Type = 'U'
AND a.NAME NOT IN  ('Name')

SET @SQL = 'SELECT ' + SUBSTRING(@columns,2,LEN(@columns))  + ' FROM #ReportRows '

EXEC(@SQL)

OUTPUT

 Id          Length
    ----------- -----------
    1           5
    2           3
    3           4
    4           5

    (4 rows affected)

@PawanKumar there are [multiple ways to aggregate strings](https://sqlperformance.com/2014/08/t-sql-queries/sql-server-grouped-concatenation) before SQL Server 2017. This answer shows the quirky update which is twice as slow as the XML method shown by Larnu. In SQL Server 2017 STRING_AGG removes the need for custom string aggregation — Panagiotis Kanavos, Feb 15 '18 at 08:32

How to select all columns except the specified columns in SQL

2 Answers2