exclude columns while using select *

Question

Is there anyway to exclude columns when using select * ? Of course I could just list the columns I want, but if say I'm querying a table with 30 columns and I want 27 of them it would a lot cleaner to write something like SELECT * EXCLUDE a, b, c

Is there anyway to do this?

I guess you'd better try and show us a picture of what you're talking about. My instinct says this is a display problem, not a query problem. — Robert Harvey, Apr 11 '13 at 17:43
@RobertHarvey to be more concrete, the problem is that select * retrieves table1.MetaTagNameID and table2.MetaTagNameID when I only want MetaTagNameID once. I'm really just looking for a clean way to write a query that selects everything but one or two columns. Assuming I have 30 columns coming back I don't want to list 29 columns in the select just so I don't have a duplicate column in the results. — evanmcdonnal, Apr 11 '13 at 17:48
It's not worth the effort. You'd have to create the SQL statement dynamically, specify the columns you want, and exclude the duplicate column. — Robert Harvey, Apr 11 '13 at 17:51
No, you can't do this with `SELECT *`. There is no `SELECT * EXCEPT (col1)`. As @Robert suggested, you'd have to go to the metadata for the two tables, eliminate dupes, and build the statement using dynamic SQL. Or you could just skip the column name in the presentation tier... — Aaron Bertrand, Apr 11 '13 at 17:54
@AaronBertrand I reworded the question sincen it was very unclear. As RH said, that would be a useful answer. If you post it I will accept. — evanmcdonnal, Apr 11 '13 at 18:03

Aaron Bertrand · Accepted Answer · 2022-01-24T23:40:14.037

Here's one way to generate a select list and eliminate duplicate columns in both tables. It assumes you already know the two tables and the relationship between them. THis uses AdventureWorks and the tables Sales.SalesOrderDetail and Sales.SalesOrderHeader. Note that columns in multiple tables with the same name are not necessarily redundant - for example an order may be modified independently of the order detail items, and now your query both (a) ignores the detail level modified dates, and (b) doesn't make it clear in the results which modified date has been exposed (unless the user reads the query text, if they have access to it).

DECLARE @sql nvarchar(max);

;WITH l AS 
(
  SELECT column_id, name FROM sys.columns 
  WHERE [object_id] = OBJECT_ID(N'Sales.SalesOrderDetail')
),
r AS
(
  SELECT column_id, name FROM sys.columns 
  WHERE [object_id] = OBJECT_ID(N'Sales.SalesOrderHeader')
)
SELECT @sql = STUFF((SELECT N',' + CHAR(13) + CHAR(10) + CHAR(9)
  + CASE WHEN l.column_id IS NOT NULL THEN 'h.' 
  ELSE 'd.' END + QUOTENAME(COALESCE(l.name,r.name)) FROM r 
FULL OUTER JOIN l 
ON l.name = r.name
ORDER BY COALESCE(l.column_id, 255), r.column_id
FOR XML PATH(''), 
TYPE).value(N'./text()[1]', N'nvarchar(max)'), 1, 1, N'');

SELECT @sql = N'SELECT ' + @sql + ' 
  FROM Sales.SalesOrderHeader AS h
  INNER JOIN Sales.SalesOrderDetail AS d
  ON h.SalesOrderID = d.SalesOrderID;'

PRINT @sql;
-- EXEC sys.sp_executesql @sql;

Results:

SELECT 
    h.[SalesOrderID],
    d.[SalesOrderDetailID],
    d.[CarrierTrackingNumber],
    d.[OrderQty],
    d.[ProductID],
    d.[SpecialOfferID],
    d.[UnitPrice],
    d.[UnitPriceDiscount],
    d.[LineTotal],
    h.[rowguid],
    h.[ModifiedDate],
    h.[RevisionNumber],
    h.[OrderDate],
    h.[DueDate],
    h.[ShipDate],
    h.[Status],
    h.[OnlineOrderFlag],
    h.[SalesOrderNumber],
    h.[PurchaseOrderNumber],
    h.[AccountNumber],
    h.[CustomerID],
    h.[SalesPersonID],
    h.[TerritoryID],
    h.[BillToAddressID],
    h.[ShipToAddressID],
    h.[ShipMethodID],
    h.[CreditCardID],
    h.[CreditCardApprovalCode],
    h.[CurrencyRateID],
    h.[SubTotal],
    h.[TaxAmt],
    h.[Freight],
    h.[TotalDue],
    h.[Comment] 
  FROM Sales.SalesOrderHeader AS h
  INNER JOIN Sales.SalesOrderDetail AS d
  ON h.SalesOrderID = d.SalesOrderID;

exclude columns while using select *

1 Answers1