asked on

MSSQL 2019 Select * [Except .'x','y','z']

Is it possible to have a Select all query except a couple of specific fields?

My use case is to return all fields from MyTable except two specific fields. The table can have some new fields added to it and my goal is to not hard code the select statement when the table layout changes.

SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'MyTable'

Open in new window

I know my options could include pushing out to a temp table and removing those two fields from the temp table for viewing, or saving the data to an array and pop the two elements. My question is if there is a way to do this as an SQL statement and perhaps 2019 has something obscure?

SOLUTION

Brian Crowe

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Pavel Celba

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Scott Fell

ASKER

All of your comments lead me to figure this out, especially Jim's about trying out a view where I figured out to use something simple in this case because I only want a list of the fields, type and char length.

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE  (TABLE_NAME = 'MyTable') AND (COLUMN_NAME <> N'field1' AND COLUMN_NAME <> N'field2')

Open in new window