September 4, 2008 at 12:38 pm
Does anyone know of a short cut to do what is simular to a select * but instead of selecting every thing selecting everything but one field or everything but 2 fields?
Usually I just specify all fields I require but I am just wondering if there is a quicker way to do it than this.
September 4, 2008 at 1:03 pm
List all but the one or two columns you don't want. Even when you do want all the columns it's better to specify the names explicitly. The table may get more columns in the future.
If you don't want to type, you can get a select statement generated from object explorer (right click table, script as select...) or you can drag the columns folder into the query window to get all the columns listed.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 4, 2008 at 1:33 pm
Appreciate the response Gila... I was hoping there was some new short cut available I currently use the method you suggested.
Cheers,
September 4, 2008 at 2:37 pm
I know what you're talking about and I have also tried to find a solution for this ... it's a PITA when you have a very large table and don't want to specify all the columns ...
Anyways, you can always do this to quickly copy/paste the column names instead of having to drag/drop them ...
SELECT
[name]
FROM sys.columns
WHERE [object_id] = object_id('TableName')
ORDER BY 1
Doesn't save much time, but eh, an alternative nonetheless.
September 4, 2008 at 3:12 pm
SELECT
[name] + ', '
FROM sys.columns
WHERE [object_id] = object_id('TableName')
ORDER BY 1
That way you don't have to manually put the commas.
I'd preffer fail solution, the guid scripting makes a good job of formatting this correctly.
September 5, 2008 at 12:29 am
SELECT
[name] + ', '
FROM sys.columns
WHERE [object_id] = object_id('TableName')
ORDER BY 1
The query above will return extra comma ',' between last column name and FROM clause resulting into error while executing the query.
September 5, 2008 at 6:30 am
I know... but it beats the hell out of adding 50 commas manually!!!
Anyhow, I still preffer the scripting option of the GUI.
September 5, 2008 at 9:34 am
You could do something like this:
declare @Columns varchar(max)
select @columns = coalesce(@columns + ',' + name, name)
from sys.columns
where object_id = object_id('orders')
and name != 'orderid'
select @columns
If you want indented formatting, with one column name per row, then try this:
declare @Columns varchar(max)
select @columns = coalesce(@columns + '
,' + name, '' + name)
from sys.columns
where object_id = object_id('orders')
and name != 'orderid'
select @columns
It'll most likely be most useful if you use the Output to Text option.
You can, of course, add as many column exclusions as you want to the Where clause.
And, of course, neither version here adds an extra comma at the end of the list.
Does either do what you need?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 5, 2008 at 9:45 am
my two cents:
This is what I use:
/*
Specify values for template parameters
CTRL+SHIFT+M
*/
DECLARE @vcTableName VARCHAR(100)
SET @vcTableName = ' '
select '[' + name + '],' as [USED FOR SELECT/INSERT]
from syscolumns
where id = object_id(@vcTableName)
order by colid
select '[' + name + '] = [' + name + '],' as [USED FOR UPDATE]
from syscolumns
where id = object_id(@vcTableName)
order by colid
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply