Hi,

Is there any way how to formulate SQL query to select all columns except
column D and E?

So if the table has the columns A,B,C,D the result woud contain only columns
A,B,C

SELECT " * except D,E"
FROM myTable

Thanks,

Lubomir

SELECT A, B, C
FROM myTable

"Lubomir" <Lubomir (AT) discussions (DOT) microsoft.com> wrote

Hello,

If you want it permamanent then you can create a view and then query the
view

CREATE VIEW A1
as
Select A,B,C from myTable

After the creation query the view

Select * from A1

THanks
Hari


"Lubomir" <Lubomir (AT) discussions (DOT) microsoft.com> wrote

The problem is, the tables are created on the fly, so I don't know what
columns the particular table will have. I know however, every table has two
columns ("help" columns) that will not be displayed, as they are used for
another purposes.

From that reason, it would be very convenient to make query like SELECT *
and to specify those 2 columns to be exclusive.

Thanks,

Lubomir

"Hari Prasad" wrote:

On Fri, 4 May 2007 09:37:00 -0700, Lubomir wrote:

Hi Lubomir,

That is indeed a problem. And it's also a sign that you are using the
database in a way that it's not intended to be used - applications that
need to create tables on the fly are almost always the result of some
bad design decisions.

Could you explain in some more detail WHY your application does not have
a fixed data model?

There is no syntax for this in SQL. (And if anyone ever proposes it, I'd
vote against it - personally, I'd rather remove the SELECT * than to
extend it!)

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

How do you manage, populate, update, etc., tables that you don't know the
structure of? How do you know you are getting the right number of columns,
with the correct names, in your UI application? How do you know that your
datasets will not break the front end? And if you're just not "displaying"
the help columns, then just don't "display" them. What you do or don't
display in the UI doesn't have to be the same as what you retrieve from the
database.

In all seriousness though, get rid of the SELECT * and start naming your
columns. It will save you a bunch of headaches down the road.

"Lubomir" <Lubomir (AT) discussions (DOT) microsoft.com> wrote

Hi Hugo,

The reason is, that that this application serves more application (like
plugins) with their own tables.

Lubomir


"Hugo Kornelis" wrote:

On Mon, 7 May 2007 09:38:00 -0700, Lubomir wrote:

Hi Lubomir,

As long as the data requirements for all these plugin applications are
relatively stable, they can each have their own set of tables that
you'll have to design and deploy once (and possibly more often, if and
when requirements change).

If the data requirements can change on a daily basis, you're probably
best advised to move away from relational databases, since they're
really designed to be used with a fixed datamodel.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

If you are talking about SQL 2005, then you can write DDL trigger and
programically create view or procedure wich select all columns from table
exept those 2.

Ramunas

"Lubomir" <Lubomir (AT) discussions (DOT) microsoft.com> wrote

Yes, that could work.

Thanks,

Lubomir


"Ramunas Balukonis" wrote: