[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1033
  • Last Modified:

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


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...
3 Solutions
Aneesh RetnakaranDatabase AdministratorCommented:
there is no such options in sql server other than specifying the other 99 columns ecplicitly
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I agree. the only "help" could be to build the sql using dynamic sql that excludes that single column.
SharathData EngineerCommented:
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'
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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.
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: http://www.red-gate.com/products/SQL_Prompt/index.htm 
Best $195 I ever spent for productivity software.
Star79Author Commented:
II knew there is no straight forward answer for this .. but got good alternative solutions.

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now