Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I need to automate writing a query in Sybase.

The table looks like this:

ID, A1, A2, A3, ..., A24, B1, B2, B3, ..., B24, etc.

I would like a query where the user can choose which columns are returned. For instance, sometimes the user can choose all the A* columns, sometimes they can choose all the B* columns, sometimes they can choose any of the columns.

I need to write a query to read values from all the columns like:

SELECT a*,b* FROM Users


SELECT * FROM Users WHERE column_name LIKE a%, b%
share|improve this question
up vote 4 down vote accepted

First: This isn't easy to implement and it's going to break very easily (by which I mean performance is going to be horrible, and it's a nice little route for SQL injection attacks if you're not very very careful). I strongly advise you to re-think what you're doing because there has to be a better way.

Second: The question as it stands doesn't actually make sense - what condition are you checking on the columns? NOT NULL? Also, do you want to AND or OR the conditions together?

To actually answer the question, though, to do this you'll need to write some dynamic SQL:

  • Link sysobjects to syscolumns, filter by the table name and the fields you're interested in.
  • Build a executable SQL statement using a cursor, then execute it.

Example below is, for each column in the Users table that starts with either 'A' or 'B', return all rows in the Users table that have a non-null value in any of those columns.

Note: I don't have a Sybase install handy to test this. Chances of it actually working are very low, but it should hopefully be enough to make you realise what a bad idea this is.

DECLARE @colname nvarchar(255),
        @sql nvarchar(4000)

SET @sql = 'SELECT * FROM Users WHERE '

    FROM syscolumns sc 
            INNER JOIN 
         sysobjects so ON =
    WHERE = 'Users'
        AND so.type = 'U'
        AND ( LIKE 'A%' OR LIKE 'B%')
OPEN colcur

    FETCH colcur INTO @colname 
    SET @sql = @sql + @colname + ' IS NOT NULL OR '


SET @sql = LEFT(@sql, LEN(@sql) - 3) -- need to trim off the trailing 'OR'

EXEC @sql
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.