SQL Anywhere脚本语法报错求助：DECLARE语句附近语法问题排查

错误信息 #

SyntaxError: Expected "--", "/*", ":=", "=", or [ \t\n\r] but "D" found.

问题定位与修正 #

虽然错误提示指向第二行的DECLARE语句，但实际是脚本中存在多处格式/语法问题导致解析器混乱，回溯报错：

1. 变量与关键字间缺少空格
   原代码中子查询里的mytable_nameAND是非法标识符，解析器无法识别：

   WHERE table_name = mytable_nameAND creator = USER_ID('DBA')
   

   修正为：

   WHERE table_name = mytable_name AND creator = USER_ID('DBA')
   

2. 注释覆盖ORDER BY子句
   FOR循环的SELECT语句中，注释直接跟在WHERE条件后，导致ORDER BY table_name被注释掉，破坏了语句结构：

   SELECT table_name FROM SYSTAB WHERE creator = USER_ID('DBA') AND table_type = 1  -- Base tables only ORDER BY table_name
   

   修正为将注释单独换行，恢复ORDER BY的有效性：

   SELECT table_name 
   FROM SYSTAB 
   WHERE creator = USER_ID('DBA') 
     AND table_type = 1  -- Base tables only
   ORDER BY table_name
   

3. 执行环境限制（可选）
   SQL Anywhere部分执行环境不支持直接运行裸BEGIN...END复合语句，若修正后仍报错，建议将脚本封装为存储过程执行：

   CREATE OR REPLACE PROCEDURE ExportTableColumns()
   BEGIN
      DECLARE myoutput_line    LONG VARCHAR;
      DECLARE mytable_name     VARCHAR(128);
      DECLARE myoutput_file    VARCHAR(255);
      
      -- Set output file path (modify as needed)
      SET myoutput_file = 'C:\\tables\\names.txt';
      
      -- Initialize output file
      UNLOAD SELECT 'Table and Column Listing' || CHAR(10) || '======================' || CHAR(10) TO myoutput_file;
      
      -- Cursor to iterate through user tables
      FOR table_loop AS table_cursor INSENSITIVE CURSOR FOR
         SELECT table_name 
         FROM SYSTAB 
         WHERE creator = USER_ID('DBA') 
           AND table_type = 1  -- Base tables only
         ORDER BY table_name
      DO
         SET mytable_name = table_name;
         -- Append table name to output
         SET myoutput_line = CHAR(10) || 'Table: ' || mytable_name || CHAR(10) || '------------------------' || CHAR(10);
         -- Append columns for current table
         SET myoutput_line = myoutput_line || (
            SELECT LIST('  ' || column_name || ' (' || domain_name || 
                       CASE WHEN char_length > 0 THEN '(' || char_length || ')' ELSE '' END || 
                       CASE WHEN scale > 0 THEN ',' || scale ELSE '' END || ')' || CHAR(10), '' )
            FROM SYSTABCOL tc
            JOIN SYSDOMAIN sd ON tc.domain_id = sd.domain_id
            WHERE table_id = (SELECT table_id 
                             FROM SYSTAB 
                             WHERE table_name = mytable_name 
                               AND creator = USER_ID('DBA') )
            ORDER BY column_id
         );
         -- Write to output file
         UNLOAD SELECT myoutput_line TO myoutput_file APPEND;
      END FOR;
      
      -- Add completion message
      UNLOAD SELECT CHAR(10) || 'Export completed: ' || CURRENT TIMESTAMP TO myoutput_file APPEND;
   END;
   
   -- 调用存储过程执行
   CALL ExportTableColumns();
   

修正后的完整脚本 #

BEGIN
   DECLARE myoutput_line    LONG VARCHAR;
   DECLARE mytable_name     VARCHAR(128);
   DECLARE myoutput_file    VARCHAR(255);
   
   -- Set output file path (modify as needed)
   SET myoutput_file = 'C:\\tables\\names.txt';
   
   -- Initialize output file
   UNLOAD SELECT 'Table and Column Listing' || CHAR(10) || '======================' || CHAR(10) TO myoutput_file;
   
   -- Cursor to iterate through user tables
   FOR table_loop AS table_cursor INSENSITIVE CURSOR FOR
      SELECT table_name 
      FROM SYSTAB 
      WHERE creator = USER_ID('DBA') 
        AND table_type = 1  -- Base tables only
      ORDER BY table_name
   DO
      SET mytable_name = table_name;
      -- Append table name to output
      SET myoutput_line = CHAR(10) || 'Table: ' || mytable_name || CHAR(10) || '------------------------' || CHAR(10);
      -- Append columns for current table
      SET myoutput_line = myoutput_line || (
         SELECT LIST('  ' || column_name || ' (' || domain_name || 
                    CASE WHEN char_length > 0 THEN '(' || char_length || ')' ELSE '' END || 
                    CASE WHEN scale > 0 THEN ',' || scale ELSE '' END || ')' || CHAR(10), '' )
         FROM SYSTABCOL tc
         JOIN SYSDOMAIN sd ON tc.domain_id = sd.domain_id
         WHERE table_id = (SELECT table_id 
                          FROM SYSTAB 
                          WHERE table_name = mytable_name 
                            AND creator = USER_ID('DBA') )
         ORDER BY column_id
      );
      -- Write to output file
      UNLOAD SELECT myoutput_line TO myoutput_file APPEND;
   END FOR;
   
   -- Add completion message
   UNLOAD SELECT CHAR(10) || 'Export completed: ' || CURRENT TIMESTAMP TO myoutput_file APPEND;
END;

内容的提问来源于stack exchange，提问作者Ian Caesar