Selecting all except for one - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Selecting all except for one

Author

Topic

smmcg
Starting Member

1 Post

Posted - 2003-01-24 : 22:50:15

Is it possible to select all fields except for one (or a list)?

eg:
SELECT (all fields except for ID and FNAME) from TABLE_NAME;

mfemenel
Professor Frink

1421 Posts

Posted - 2003-01-24 : 23:18:50

Looking for something like this?
select * from table_name
where criteria_field not in(x,y,z)

Mike
"oh, that monkey is going to pay"

mfemenel
Professor Frink

1421 Posts

Posted - 2003-01-24 : 23:18:54

Looking for something like this?
select * from table_name
where criteria_field not in(x,y,z)

Mike
"oh, that monkey is going to pay"

LarsG
Constraint Violating Yak Guru

284 Posts

Posted - 2003-01-25 : 12:58:34

No, you must supply the columns explicitly.

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-01-25 : 15:06:10

DECLARE @COLUMNS NVARCHAR(4000)
DECLARE @SQL NVARCHAR(4000)

SET @COLUMNS = ''

SELECT @COLUMNS = @COLUMNS + ',' + COLUMN_NAME FROM INFORMATION_SCHEMA.Columns
WHERE TABLE_CATALOG = 'Test' AND TABLE_NAME = 'test' AND COLUMN_NAME NOT IN ('pid')

SET @COLUMNS = SUBSTRING(@COLUMNS,2,LEN(@COLUMNS))

SET @SQL = 'SELECT ' + @COLUMNS + ' FROM test'

EXEC sp_executesql @SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources