ASA SQL Reference
SQL Statements
Use this statement to validate a table in the database.
VALIDATE TABLE [ owner.]table-name
[ WITH { DATA | EXPRESS | FULL | INDEX } CHECK ]
WITH DATA CHECK If you have LONG BINARY, LONG VARCHAR, TEXT, or IMAGE entries, they may span more than one database page. In addition to the default checks, this option instructs the database server to check all pages used by each entry.
WITH EXPRESS CHECK In addition to the default and WITH DATA checks, check that the number of rows in the table matches the number of entries in the index. This option does not perform individual index lookups for each row. This option can significantly improve performance when validating large databases with a small cache.
WITH FULL CHECK In addition to the default checks, carry out a DATA CHECK and an INDEX CHECK.
WITH INDEX CHECK In addition to the default checks, validate each index on the table. For information on index validation, see VALIDATE INDEX statement.
With no additional options, VALIDATE TABLE scans every row of a table. For each entry that is in an index, it checks the validity of the database page that the entry starts on, and checks that an entry for the row exists in the proper index. The VALIDATE TABLE statement also ensures, for each index in the table, that the number of rows referenced by the index is not greater than the number of rows in the table.
This default validation is sufficient for most purposes. Options are provided for additional validation, which may be helpful in unusual circumstances. Depending on the contents of your database, these additional checks may significantly extend the time required to validate.
If the table is corrupt, an error is reported. If you do have errors reported, you can drop all of the indexes and keys on a table and recreate them. Any foreign keys to the table will also need to be recreated. Another solution to errors reported by VALIDATE TABLE is to unload and reload your entire database. You should use the -u option of DBUNLOAD so that it will not try to use a possibly corrupt index to order the data.
Must be the owner of the table, have DBA authority, or have REMOTE DBA authority (SQL Remote).
None.
SQL/92 Vendor extension.
SQL/99 Vendor extension.
Sybase VALIDATE TABLE is not supported in Adaptive Server Enterprise. The procedure dbcc checktable provides a similar function.
WITH EXPRESS CHECK option This option is only supported for databases created with Adaptive Server Anywhere version 7.0 or later.