Avatar of Scott Fell
Scott Fell
Flag for United States of America 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?
SQLMicrosoft SQL Server

Avatar of undefined
Last Comment
Scott Fell

8/22/2022 - Mon
SOLUTION
Brian Crowe

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
Jim Horn

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
Pavel Celba

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
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



All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck