I have data on MS SQL Server that have over 60 columns, I would like to
select 30 of these columns that may or may not contain NULL and I don't
want to write out all 30 columns and check for IN NOT NULL. Does anyone
know how to do that?

Not trying to be harsh, but.... start typing. :)

Is this a recurring problem, or are you shooting for a one time
solution?  If one-time, the easiest way to do it is to just write the
SQL query that you're attempting to avoid.  If you're trying to develop
some sort of administrative tool to help you validate data, you could
script something that uses dynamic SQL (using the syscolumns table).
That's not something that I would advocate giving to the average user.

Stu


timhz***@gmail.com wrote:
Show quoteHide quote

You must perform each IS NOT NULL test in the query.  However, you can
save some typing.  This will generate a query to get you started, just
copy the results.

declare @tbl varchar(50)
set @tbl = 'Categories'

SELECT CASE WHEN C.ORDINAL_POSITION =
                 (select min(ORDINAL_POSITION)
                    from INFORMATION_SCHEMA.COLUMNS C2
                   where C2.TABLE_NAME = @tbl
                     and C2.IS_NULLABLE = 'YES')
            THEN 'SELECT * FROM ' + C.TABLE_NAME +
                  char(13) + CHAR(10) +
                 ' WHERE '
            ELSE '   AND '
       END +
       C.COLUMN_NAME + 'IS NOT NULL' + char(13) + CHAR(10)
  FROM INFORMATION_SCHEMA.COLUMNS C
WHERE C.TABLE_NAME = @tbl
   AND C.IS_NULLABLE = 'YES'
ORDER BY C.TABLE_SCHEMA, C.TABLE_NAME, C.ORDINAL_POSITION


On 29 Jun 2006 08:48:16 -0700, timhz***@gmail.com wrote:

Show quoteHide quote

No, there are no lazy shortcuts in T-SQL to select "a set of columns".

But, there are lazy shortcuts to generating the list so that you can create
a valid and reasonable T-SQL statement more quickly.  What are you using,
6.5, 7.0, 2000, 2005?  In Query Analyzer or Management Studio, when you
expand a table and you see a folder called columns, drag it to the query
window.  Voila, like magic, huh?

Laziness is not enough of a reason to use SELECT * (or seek a similar
alternative).

A






<timhz***@gmail.com> wrote in message
Show quoteHide quote

before you begin anything .. you might want to consider clean up the
data to update those columnes with a default value.

ie.

update table
set column = ''
where column is null

possibly put them in a temporary table?

timhz***@gmail.com wrote:
Show quoteHide quote

Contrary to Developer opinion, sometimes NULL is an appropriate value.
(Albieit, not as often as it is used just because folks don't take time to
understand the implications.)

A question I posit is: How many responses are there to a Yes/No question?

The correct response is Four. Yes, No, Not Answered, Not Applicable.

The use of the data determines whether or not there is a distinction between
the last two. For example, if I'm analyzing survey results, I wouldn't want
to confound the analysis by combining Not Answered and Not Applicable.

Show quoteHide quote

Identity keys in a One-to-Zero-or-Many relationship
Insert in every ID
SQL Newbie
Iteration in a stored procedure
How to return error from CLR Stored Procedure