0

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.

Krischu
  • 1,024
  • 2
  • 15
  • 35

1 Answers1

1

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.

AHiggins
  • 7,029
  • 6
  • 36
  • 54
  • Thanks. I pasted this right into a console to run the SQL. Sure, I shouldn't expect this to run without throwing an error. But to understand the long quoted 'SELECT ....' line, could you elaborate on this? I'm getting: `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 ^ Error: 00318 EXE DNN Data is not numeric` – Krischu May 21 '15 at 07:17
  • A colleague was so kind to modify the statement to fit SQLBase syntax:`SELECT tbname ||';'|| name ||';'|| 'SELECT ''' || tbname || ''' AS TableName, ''' || name || ''' AS ColumnName, MAX(@LENgth(' || name || ')) AS ColumnLength FROM ' || tbname ||'; ' AS Query FROM sysadm.syscolumns WHERE coltype='LONGVAR' ORDER BY TBNAME; ` – Krischu May 21 '15 at 10:26
  • A last question: Since the above query generates a list of queries, would it be possible to pack this into a procedure and execute this "array" or table of queries? Is it possible in SQL to execute SQL code that is stored in a table row/column? Similar to the unix shell ` (backquote). I'm pretty sure it is. – Krischu May 21 '15 at 10:31
  • @Krischu, sorry for the late reply! Vacation = no computer use. I'm glad that you were able to find an answer despite my lack of SQLbase syntax. Yes, I do believe it would be possible to execute this "array"; SQL Server calls this `dynamic SQL`, but I think I've already proven that I can't translate between the two! – AHiggins Jun 01 '15 at 12:39