excluding fields in SELECT statement

[ric]

Hi,

Is there a way to exclude fields in a query other than just including
the ones you want. If there are 20 fields and you want to see all but
3, it would be a lot easier to exclude the 3.

Thanks

[Dan Guzman]

No, there is no Transact-SQL syntax for this. However, you can use Query
Analyzer to generate a SELECT statement for the desired view or table and
then remove the unneeded columns.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"ric" <rt*****@yahoo.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...

Hi,

Is there a way to exclude fields in a query other than just including
the ones you want. If there are 20 fields and you want to see all but
3, it would be a lot easier to exclude the 3.

Thanks

[David Portas]

"ric" <rt*****@yahoo.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...

Hi,

Is there a way to exclude fields in a query other than just including
the ones you want. If there are 20 fields and you want to see all but
3, it would be a lot easier to exclude the 3.

Thanks

No. It isn't difficult to list just the columns you want. In Query Analyzer
you can drag the column list from the Object Browser pane so no typing is
required.

Note that it is also good practice to avoid using SELECT * in production
code. Using * instead of listing just the required columns is not only
inefficient, it also impacts the reliability and maintainability of your
code.

--
David Portas
SQL Server MVP
--

[--CELKO--]

>> Is there a way to exclude fields [sic] in a query other than just including the ones you want. If there are 20 fields [sic] and you want to see all but 3, it would be a lot easier to exclude the 3. <<

Short answer: No. Have you ever seen such a thing in any other
programming language? The closest thing I know is FILLER in Cobol.

Rows are not records; fields are not columns; tables are not files. It
is not easier; it is dangerous. If the base table is altered, your
syntax would not see the changes and would screw up. A list of column
name is easy to generate from the schema information tables with a
tool.

[ric]

Great!

Forgot to mention I was looking for a faster way to work in the Query
Analyzer.

you can use Query Analyzer to generate a SELECT statement for the desired view...

Is this something other than dragging over columns into the SELECT
statement you're building?

Thanks,
Ric

[Erland Sommarskog]

ric (rt*****@yahoo.com) writes:

you can use Query Analyzer to generate a SELECT statement for the desired
view...

Is this something other than dragging over columns into the SELECT
statement you're building?

I guess this is what Dan had in mind. Myself, when I need to do this, I
usually do a SELECT * FROM tbl WHERE 1 = 0 in text mode, copy and paste
the headers into Textpad, where I have a macro so I with a keypress can
replace the spaces with commas, and then cut and paste back.

There is a new product PromptSQL which claims to provide intellisense to
Query Analyzer. Since I am not fond of intellisense myself, I have not
tried it. But somehing that expands a * would be a great thing for such a
tool - maybe they have it?
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Dan Guzman]

> I guess this is what Dan had in mind.

I used to use that method in the old days (pre- SQL 2000) but I was
referring to the following technique:

1) right-click on the desired table in the QA object browser

2) select Script Object to New Window as--> Select

3) remove unwanted columns
--
Hope this helps.

Dan Guzman
SQL Server MVP

"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn**********************@127.0.0.1...

ric (rt*****@yahoo.com) writes:

you can use Query Analyzer to generate a SELECT statement for the
desired
view...

Is this something other than dragging over columns into the SELECT
statement you're building?

I guess this is what Dan had in mind. Myself, when I need to do this, I
usually do a SELECT * FROM tbl WHERE 1 = 0 in text mode, copy and paste
the headers into Textpad, where I have a macro so I with a keypress can
replace the spaces with commas, and then cut and paste back.

There is a new product PromptSQL which claims to provide intellisense to
Query Analyzer. Since I am not fond of intellisense myself, I have not
tried it. But somehing that expands a * would be a great thing for such a
tool - maybe they have it?
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[promptsql]

Expanding '*' is something people have asked for, and its on the list,
but not there yet -
http://www.promptsql.com/known_bugs_...hancements.htm

Regards,
Damian (PromptSQL developer)
Erland Sommarskog wrote:

ric (rt*****@yahoo.com) writes:

you can use Query Analyzer to generate a SELECT statement for the desired
view...

Is this something other than dragging over columns into the SELECT
statement you're building?

I guess this is what Dan had in mind. Myself, when I need to do this, I
usually do a SELECT * FROM tbl WHERE 1 = 0 in text mode, copy and paste
the headers into Textpad, where I have a macro so I with a keypress can
replace the spaces with commas, and then cut and paste back.

There is a new product PromptSQL which claims to provide intellisense to
Query Analyzer. Since I am not fond of intellisense myself, I have not
tried it. But somehing that expands a * would be a great thing for such a
tool - maybe they have it?
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[promptsql]

I said expanding '*' was on the list of things to be implemented --
we've just released PromptSQL 1.2 Beta one, and this was one of the new
features.

You can now type
SELECT *[TAB] FROM Orders, Customers
and the * will be expanded to insert all the columns in Orders and
Customers, prefixed by appropriate aliases. The separator is
customizable.

Or you can type:
SELECT o.*[TAB] FROM Orders o, Customers and the o.* will be replaced
by a list of all Orders columns, prefixed by "o."

Regards,
Damian (PromptSQL developer)

http://www.promptsql.com