How to Exclude a column for a select query but still use *

Posted on 2009-12-22
Last Modified: 2012-08-13

This might be little odd ...but this is what i want to do.
Using select * which obviously returns all the columns but i want to avoid one column but display all other columns, how do we do it....say i have a table with 100 columns but i want to show only 99 columns?

Any help will be appreciated...
Question by:Star79
    LVL 75

    Expert Comment

    by:Aneesh Retnakaran
    there is no such options in sql server other than specifying the other 99 columns ecplicitly
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    I agree. the only "help" could be to build the sql using dynamic sql that excludes that single column.
    LVL 40

    Accepted Solution

    declare @columns nvarchar(100),@sql nvarchar(2000)
    set @sql = 'select '
    set @columns = (
    select distinct rtrim(substring(isnull((select ','+ column_name
     where t1.table_name  = 'Table Name' and column_name <> 'Column Name'
       for xml path('')),' '),2,2000)))

    set = @sql + @columns + ' from YourTable'
    LVL 4

    Assisted Solution

    By the way... if it's that you just don't want to type out the 99 columns by hand, from within the SQL Server Management Studio, you can right-click on the table in question then choose "Script Table as" | "Select to" | Clipboard. It will create a basic select statement for you with all the column names. Just modify the select statement as needed.
    LVL 14

    Assisted Solution

    Another time saver would be to pick up a copy of SQL Prompt by Red-Gate Software. This add-in allows you to format your code (VERY HANDY) with one mouse click, as well as a whole library of code snippets and very good intellisense.
     One I use ALL the time is "ssf". When I type that followed by tab, it explodes out to "SELECT * FROM " then all I have to type is the table name.
    Once I have that, then I put the cursor just to the right of the * and hit tab and it blows out to the entire list of fields from the table. Then you could just delete the fields you don't want from your query.
    The add-in works for the old Query Analyzer, SSMS 2005 & 2008 and Visual Studio.
     Check it out at:
    Best $195 I ever spent for productivity software.

    Author Closing Comment

    II knew there is no straight forward answer for this .. but got good alternative solutions.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
    In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now