Skip to Content

Finding total number of tables in union query in Sybase

I am writing a code which supports different versions of Sybase ASE. I am using union queries and the problem is that different version of Sybase ASE supports different number of tables in union query. The union query is dynamic and will be formed depending on the number of database present in the server.

Is there any way in which I can find the max number of tables supported by a particular Sybase ASE? The only solution that I know right now is to fetch the version using query and pick out the version number from the result and set the number accordingly in the code. But this is not a very good solution. I tried checking if there are any tables which have stores this value but nothing came up. Can anyone suggest any solution for this?

I had come across dbcc serverlimits but I am not sure how to use it in the code as it publishes only on the isql console. Thanks in advance.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Apr 25, 2017 at 10:25 PM

    Here's a bit of a kludge to allow you to capture/use the output of dbcc serverlimits in a T-SQL batch:

    -- enable trace file for your spid
    set tracefile '/tmp/my_serverlimits' for @@spid
    -- dump dbcc serverlimits output to your tracefile
    dbcc serverlimits
    -- turn off tracing
    set tracefile off for @@spid
    -- enable external file access:
    sp_configure 'enable file access',1
    -- create proxy table pointing at the trace file
    create proxy_table dbcc_serverlimits external file at '/tmp/my_serverlimits'
    -- find our column name ('record' of type varchar(255) in this case)
    sp_help dbcc_serverlimits
    -- extract the desired row; store the 'record' value in a @variable
    -- and parse for the desired info ...
    select * from dbcc_serverlimits where lower(record) like '%union%'
     Max number of user tables overall in a statement using UNIONs      : 512
    Add comment
    10|10000 characters needed characters exceeded

  • Apr 25, 2017 at 08:52 PM

    Using the version as you are doing is the most practical approach I can think of. I don't think the limit is available anywhere in form you can easily query.

    Add comment
    10|10000 characters needed characters exceeded

  • Apr 25, 2017 at 12:23 PM

    dbcc serverlimits would be my first answer.


    My second answer would be to redesign your query/process to eliminate the need to create a 'large' union ...


    There are several downsides to large queries (in terms of the number of tables; in terms of the number of sub-queries in a UNION query):

    - compile time (typically) increases

    - the compilation phase may run out of time before finding the 'best' query plan (thus leaving you with a poor query plan)

    - a poor query plan is more likely to be generated as the chances of the optimizer finding missing/out-of-date stats increases

    - a poor query plan (usually) leads to poor execution times (in a worst case scenario a query could run for days)

    - the larger the query the harder it becomes to troubleshoot said query (whether for performance or accuracy reasons)


    Consider what happens if you run your process on a dataserver with 100's of databases (a few years ago I was working with a client that had upwards of 500 databases in one ASE dataserver); will your dynamic query approach blow up? Will you generate a union query that's so large you'll never be able to obtain a 'good' query plan?

    What happens if your query hits the limit on the number of tables allowed in a query? I'm guessing your process will need to create multiple/separate queries, yes?


    ... so, to eliminate (or at least greatly reduce) the (above) potential issues I'd probably opt for a design similar to the following:

    - create a #temp table to hold intermediate results

    - use a cursor to step through a list of all databases

    - for each database run a query that populates the #temp table with the desired data from said database

    - once all databases have been processed, run a final query against the #temp table to generate the desired final/master result

    Another (obvious) benefit is that you no longer need to worry about running into limits on the number of tables in a query.

    Add comment
    10|10000 characters needed characters exceeded