Post reply

Is there a short cut (Like 'SELECT *') for selecting all but a few fields in a table??

  • October 4, 2013 at 2:58 am

    #278694

    Hello,

    I have a table of like more than 50 columns, and my problem here is that I want ALL but 2 or 3 column names in my SELECT clause! Is there any way to do this without having to write all the field names in the SELECT clause?

    What I mean is just as we use '*' to display all the columns, is there any such way like 'SELECT * EXCEPT (Column1,Column2) ' (Just an example!) to do this??

  • October 4, 2013 at 3:06 am

    #1655787

    I have used the following trick in such situations

    Select '['+name+'],' from syscolumns where id=object_id('tableName')

    and name not in ('a','b')

    You can use the result in the seleect clause

    ----------------------------------------------------------------------------------------------------------------------------------------------------
    Roshan Joe

    Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • October 4, 2013 at 3:10 am

    #1655789

    You could create a view that only has the columns you are interested in.

    The SQL Guy @ blogspot[/url]

    @SeanPearceSQL

    About Me[/url]

  • October 4, 2013 at 3:32 am

    #1655805

    Thank you very much 🙂

  • October 4, 2013 at 7:22 am

    #1655891

    naxysch (10/4/2013)

    Hello,

    I have a table of like more than 50 columns, and my problem here is that I want ALL but 2 or 3 column names in my SELECT clause! Is there any way to do this without having to write all the field names in the SELECT clause?

    What I mean is just as we use '*' to display all the columns, is there any such way like 'SELECT * EXCEPT (Column1,Column2) ' (Just an example!) to do this??

    In SSMS you can drag the columns "folder" in the object explorer to a code window and it will give you a nice comma separated list of all the columns. Then you just remove the ones you don't want.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply