Select all columns except some columns

Question

I have a large number of columns in my table, like 20-30. I want to select all except 3-4 of the columns. Is there a way to to SELECT * EVERYTHING BUT COLUMNS (Address,Name etc...) FROM MyTable If not, then would such a functionality be useful ?

http://stackoverflow.com/questions/413819/select-except – mr.Reband Dec 19 '13 at 21:40 — mr.Reband, Dec 19 '13 at 21:40

Valentin Petkov · Answer 1 · 2013-12-19T22:01:56.773

2

--Just change table name and put NOT IN columns

   DECLARE @cols AS NVARCHAR(MAX),   @query  AS NVARCHAR(MAX)
   select @cols = STUFF((SELECT  N',' + QUOTENAME(c.name) 
   FROM sys.tables AS t
   INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
   WHERE t.name = 'tagCloudLibrary'
   and c.name not in ('langID')

        FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)')  ,1,1,'')


 set @query = N'SELECT ' + @cols + N' from tagCloudLibrary'
     execute sp_executesql @query;

edited Dec 19 '13 at 22:01

answered Dec 19 '13 at 21:55

Valentin Petkov

1,570
18
23

+1 With two notes: Instead of `WHERE t.name = 'tagCloudLibrary'` I would use `WHERE {t|c}.object_id = OBJECT_ID(N'dbo.tagCloudLibrary')` ... and `... from dbo.tagCloudLibrary`. – Bogdan Sahlean Dec 19 '13 at 22:03
@Bogdan I actually wouldn't use `OBJECT_ID` - [it doesn't observe outer isolation semantics](http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=432497) and also makes it much harder to reuse these types of queries across databases. Join to `sys.schemas` instead. – Aaron Bertrand Dec 19 '13 at 22:26
@AaronBertrand Thanks for this Connect item. OBJECT_ID hasn't an easy task because it may be called with DB name (`SELECT OBJECT(N'DB1.dbo.Table1')`) and current session may have another DB active (ex: DB2; `USE DB2; SELECT OBJECT(N'DB1.dbo.Table1'))` + These DB may have different Isolation/Snapshot Isolation settings (DB1:RCSI & DB2/current session:SI). What should do OBJECT_ID in these scenarious ? – Bogdan Sahlean Dec 20 '13 at 05:24
@Bogdan like I said, instead of relying on OBJECT_ID() *function* to identify an object by schema + table, you join to sys.schemas instead. – Aaron Bertrand Dec 20 '13 at 11:48
Personally I will never use approach with embedded SQL for task like this... I don't understated why there are question like this to promote themselves? but for me to answer is just to keep myself in shape to write some code... – Valentin Petkov Dec 20 '13 at 16:32

score 2 · Accepted Answer · answered Dec 19 '13 at 22:19

2

In SSMS, open Object Explorer, right-click on table -> "Script Table As" -> "SELECT To" -> "New Query Editor Window"

Delete the columns you don't want and run the query. It does all the typing for you.

answered Dec 19 '13 at 22:19

Anon

10,660
1
29
31

I knew this, I guess it is the only simple way of doing what i want without any alteration to the database. I guess i could also do select top 1000 and remove the stuff i don't need. – Steam Dec 19 '13 at 23:02

score 1 · Answer 3 · answered Dec 19 '13 at 21:44

1

It may be best to just create a VIEW on that table, without the columns you don't want to see. Then you can SELECT * from the VIEW.

answered Dec 19 '13 at 21:44

BWS

3,786
18
25

Select all columns except some columns

3 Answers3