Can I SELECT *, NOT Col1 FROM Tblname; 1

[UBfoolin]

My table has a very large number of columns. Is there a simple way to select ALL of the fields in a query except one of them?

Something like:

SELECT *, NOT Col1 FROM Tblname;

Thanks in advance,
Dan

 

[bborissov]

Am I missing something (again), Isn't that enough w/o any loops:

select @fooq = @fooq + colname + ',' from @t

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[fredericofonseca]

guys/gals. your code is too simple. a child could do it.

Please give me something more challenging.


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[bborissov]

CREATE PROCEDURE fooProc(
@tablename varchar(100),
@columnexclude varchar(500))
AS
BEGIN
declare @fooq nvarchar(4000)
SET @fooq = 'select '

SELECT @fooq = @fooq + Column_Name + ','
FROM INFORMATION_SCHEMA.Columns
WHERE Table_Name = @tablename AND
CHARINDEX(Column_Name + ',', @columnexclude+',') = 0

SET @fooq = left(@fooq, len(@fooq) - 1) + ' from ' + @tablename
--execute the constructed query
execute sp_executesql @fooq
END

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[AlexCuse]

Boris - you make it that easy, and it starts to look like a good idea

Still, nice one!

[small]----signature below----[/small]
I'm pushing an elephant up the stairs

My Crummy Web Page

 

[bborissov]

Grrrrrr,
I should added the same comment as you

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[danvlas]

My table has a very large number of columns"

Really? How many? More than 50? Why is that? Is there some more normalization possible?

If you post the structure, the guys around here will be really more than happy to explain you why you shouldn't use ANY of the solutions they posted just because it was possible and it was not very glorious to just say 'no', like George did in the first place...

That's for a table. However, if a view has a public scope, there is always the possibility of building a 'client dynamic SQL' which would return the data needed, summarized as needed, just by sending the command string to the server.

And to all:
Dan (not me) looks like a client programmer, not a database guy. Things are seen from a totally different perspective.


Hey....UBFoolin!...still around?...still alive?...still wanting to know the '* but ColumnName' thing...?
HEY!...HEy!...Hey!...hey!...whatever


Dan


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[SQLDenis]

boris, your code and the others has a flaw, can you spot it

create table BoneHead(BoneHeadid int identity,[look Spaces] varchar(666))

select * from information_schema.columns
where table_name = 'BoneHead'

so you need either brackates

or quotename

example

select quotename(column_name)
from information_schema.columns
where table_name = 'BoneHead'


select '[' +column_name + ']'
from information_schema.columns
where table_name = 'BoneHead'

anyway all this stuff is a bad idea IMNSHO
What's next? Calling columns fields? Calling Rows Records?




Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[bborissov]

Because I never named my columns to need some brackets around them, I assumed that others does so
Why I should make my life miserable by naming columns with keywords or have spaces in it? If I ever need something like this I'll do in in my query, not in table

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[SQLDenis]

Why would you ever need to script this out either....just drag the columns from the object explorer and delete the column that you want

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[bborissov]

Agreed!

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP