selectsubquerysqlbase

finding length of longest coulmn in a database - SELECT subquery

My skills in SQL are limited:

I have a database (SQLBase in this case) that has a couple of LONGVARs in columns. I'm searching for the actual length of all COLUMNS that have a particular type.

SELECT tbname,name FROM sysadm.syscolumns where coltype='LONGVAR';

The above statement works. It gives me all tables and the respective column names that have a LONGVAR datatype. Now I would like to take these data and search through all the respective tables (the rows, so the data) and find the lengths of the respective LONGVAR columns (to find the maximum for instance, or those above a certain limit).

I have the idea that it can be solved with a subquery of nested SELECT statement but have no idea how to formulate the statement.

Solution

  • I don't have any real knowledge of SQLbase, so I may be off-base here: but if I was trying to do this on SQL Server, a simple approach would be to do something like the following:

    SELECT 
    tbname,
    name, 
    'SELECT ''' + tbname + ''' AS TableName, ''' + name + ''' AS ColumnName, MAX(LEN(' + name + ')) AS ColumnLength FROM ' + tbname + ' -- add a WHERE clause here if needed' AS Query
FROM sysadm.syscolumns 
WHERE coltype='LONGVAR';

    This will output a set of values, which you could then copy/paste into a new query editor window and examine before running.

    Other, more complex solutions would involve dynamic SQL that automatically executes each of these statements; but again, not knowing much about SQLbase, this is where I would start.