Remove specific fields from query results. 2

[bbull]

1. Suppose you have a table with 100 fields.
2. You don't want to alter the table in any way.
3. You want to select everything from all fields except one.
4. You don't want to type a SELECT statement naming the 99 fields you do want.

Can this be done using a SQL statement?

Thanks for any ideas.
Bill
inre@cox.net

 

[fluteplr]

Nope.

If you do this a lot create a view listing all the fields except the one you want and then do a select * from the view.

 

[SemperFiDownUnda]

you have a table with 100 columns?
I'd be asking questions to who ever did your datamodel

 

[plantj]

You can get all the column names for a table called MUMBLE with

select
c.name
from
syscolumns c
join sysobjects o
on c.id=o.id
where
o.name="mumble"

You could extend this approach to exclude those columns that are BLOBS for instance.

 

[bbull]

Woohoo! yes, that did. Thanks very much plantj. And thank you to everyone who took the time to respond.

 

[bbull]

Woops, I spoke too soon. Unless I've misunderstood plantj's script I believe this will return the records of all the fields(columns) except those records where o.name is "mumble". However, it will still include ALL of the columns (32 in syscolumns) in the return results. I'm sorry if my original post was unclear.

Let's say I wanted to return all the columns (and records) from syscolumns except the column named "name" using a SQL statement of some kind, in order to avoid having to type all of the other column names that I did want.
How could that be done?

Thank you,
Bill

 

[fluteplr]

Did you actually read my post?

There is no way to do a select except in sql server.

Create a view without the field and select on the view.

Yes it is some work the first time, learn to live with it.

 

[LFCfan]

If you are in SQL Server 2000, then in QA if the object browser is up (tools\Object Browser\Show/Hide) you can right click on the table you're interested in and script it to a window as "Select". This will create a

select field1, field2,...,field100 from MyTable

and you can then just delete the two or three fields you dont want

 

[bbull]

Thank you, fluteplr. I did read your post and I appreciate it. However, in creating a view I'm still required to list(type) the columns that I do want. Essentially, doing a SELECT on the original table in order to create the view.

The question came up recently as to whether or not it's possible to do some kind of query on a table and instead of excluding individual records to exclude entire columns from the result without having to write out all of the column names that you do want. The point of doing it in script is that I frequently work in different versions of FoxPro, Access and SQL Server and I was looking for something that might be done with all of them.

Thanks,
Bill

 

[bbull]

Ifcfan, thanks very much. That is a time saver. I'll remember that for the future.

Bill

 

[sunila7]

Hi,

A code something like this will do wht u r looking for

Declare @fieldnames varchar(6000)
Declare @SQL varchar(8000)
Declare @tblname varchar(30)
Declare @fldnamenottobeIncl varchar(50)
set @fldnamenottobeIncl ='urfldname'
Set @tblName = 'urtblname'
Set @fieldnames = ''

Select @fieldnames= @fieldnames +column_name+ ',' from INFORMATION_SCHEMA.columns
where table_name=@tblname and column_name <> @fldnamenottobeIncl

set @fieldnames=substring(@fieldnames,1,len(@fieldnames) -1)
Set @SQL= 'SELECT ' + @fieldnames + ' FROM ' + @tblname
print @SQL

exec(@SQL)

Hope it helps

Sunil

 

[bbull]

Hi Sunil,
Wow, yes that works! I can tweak this for the other DBs if I can find an equivalent for INFORMATION_SCHEMA. Thanks very much. And thank you to all for your help.

Bill

 

[mikey2nicey]

Hi Sunil,

Your code does just what I was looking for so thanks very much! A star for a star!

Regards,
Mike


mikey2nicey
&quot;Dream, but don't quit your day job.&quot;