Any way to select all fields except 1?
in [Programming SQL]
|
Prev: Stored procedure, delete from multiple tables
Next: Organization Hierarchy from Employee Master Table
From: Nathan on 10 Feb 2010 17:04 Yes, though it can be high I/O depending on the table here is a workaround i found for it. Select * into #temp from table alter table #temp drop column column_name Select * from #temp Ken wrote: Any way to select all fields except 1? 29-Jun-07 Is there a way to select all fields from a table except one without specifing every field? Example: Select U.*, -U.Birthday (psuedo code) From Users U Where FirstName = 'Bill' Or could I select it into a Cursor or something and then slice off the fields I don't want? Thanks! Pierce Previous Posts In This Thread: On Friday, June 29, 2007 10:50 AM Ken wrote: Any way to select all fields except 1? Is there a way to select all fields from a table except one without specifing every field? Example: Select U.*, -U.Birthday (psuedo code) From Users U Where FirstName = 'Bill' Or could I select it into a Cursor or something and then slice off the fields I don't want? Thanks! Pierce On Friday, June 29, 2007 10:57 AM Russell Fields wrote: Ken,Naturally (as you know) there is no column subtractor syntax, so you will Ken, Naturally (as you know) there is no column subtractor syntax, so you will need to specify the columns you want. But, to make it easier, from the Management Studio (or Query Analyzer, depending on SQL version) Object Explorer window, select the table's Columns node and drag to the query window. That will copy all the column names is. Then delete the one column that you do not want. RLF "Ken" <notreal(a)oisudfoaijweflksjflikasjdfoiweflkasdjf.com> wrote in message news:uO2uJzluHHA.4476(a)TK2MSFTNGP03.phx.gbl... On Friday, June 29, 2007 10:59 AM Tom Moreau wrote: In SSMS, just drag the Columns folder of your table from the Object Explorer In SSMS, just drag the Columns folder of your table from the Object Explorer into your query window. Then, just delete the unwanted column from the query. -- Tom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS SQL Server MVP Toronto, ON Canada https://mvp.support.microsoft.com/profile/Tom.Moreau "Ken" <notreal(a)oisudfoaijweflksjflikasjdfoiweflkasdjf.com> wrote in message news:uO2uJzluHHA.4476(a)TK2MSFTNGP03.phx.gbl... Is there a way to select all fields from a table except one without specifing every field? Example: Select U.*, -U.Birthday (psuedo code) From Users U Where FirstName = 'Bill' Or could I select it into a Cursor or something and then slice off the fields I don't want? Thanks! Pierce On Friday, June 29, 2007 11:00 AM Aaron Bertrand [SQL Server MVP] wrote: Why would you want to do that? Why would you want to do that? Can't you just drag the columns node from Object Explorer, which will give you a comma-separated list of column names that you can prune. No. On Friday, June 29, 2007 12:09 PM Ken wrote: Thanks for the tip. Thanks for the tip. I have never used a graphical interface for writing stored procedures, always done it by hand in VisualStudio.Net. I will have to check out the Management Studio. Submitted via EggHeadCafe - Software Developer Portal of Choice SharePoint List Usage and Statistics http://www.eggheadcafe.com/tutorials/aspnet/892bae83-5b96-4275-95fd-9723a79fdb14/sharepoint-list-usage-and.aspx
From: bill on 11 Feb 2010 16:59
The only downside of drag and drop is that puts all the columns on a single line, which I don' like. Well designed tables generally don't have many columns, so reformatting isn't much of a time sync. However, many commercial apps have poorly designed tables with literally hundreds of columns. It's a pain to reformat the single giant list to a "one column on each line" format. This query will help: SELECT ',' + COLUMN_NAME + '' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '<mytable>' It will make a list like this: ,COLUMN_ONE ,COLOUMN_TWO ,COLUMN_THREE etc. Just knock the leading comma off the first row and you're ready to go. Thanks, Bill |