Select * except field7, field8

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)

Select * except field7, field8

Author

Topic

leahsmart
Posting Yak Master

133 Posts

Posted - 2003-03-11 : 11:27:48

Hi,

Is there any way to say I want to select all the fields in this table except these ones?

Select mytable.* EXCEPT mytable.field7, mytable.field8

I only ask as I have loads of fields in my table and it would be nice if I did not have to name them all.

Thanks

Leah

chadmat
The Chadinator

1974 Posts

Posted - 2003-03-11 : 11:44:54

Nope. You have to name them all.

-Chad

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-03-11 : 12:22:29

If you are using Query Analyzer 2000, then you can grab the SELECT statement from the object browser by going to the table, right-clicking on it, then select script to new/clipboard as...SELECT. Then just remove the columns that you don't want to appear. QA 2000 comes with a lot of handy features that make coding much faster.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2003-03-11 : 14:07:16

I always say, a good dba is a lazy dba. You can try the following. Just save it and use it instead of having to type a select everytime.

Good Luck

Brett

USE Northwind
Go

Declare @TBNAME sysname

SET @TBNAME = 'Employees'

SELECT SQL FROM (
SELECT 'SELECT ' + COLUMN_NAME As SQL, TABLE_NAME, 1 As SQLGroup, 1 As RowOrder
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TBNAME AND ORDINAL_POSITION = 1
UNION ALL
SELECT ' , ' + COLUMN_NAME As SQL, TABLE_NAME, 1 As SQLGroup, ORDINAL_POSITION As RowOrder
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TBNAME AND ORDINAL_POSITION <> 1
UNION ALL
SELECT ' FROM ' + TABLE_NAME As SQL, TABLE_NAME, 2 As SQLGroup, 1 As RowOrder
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = @TBNAME
) As XXX
ORDER BY TABLE_NAME, SQLGroup, RowOrder

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources