Is it possible to determine whether an existing view in Sybase ASE (v15.7) has the WITH CHECK OPTION clause? I have created 2 views that are identical except for that clause, and the entries in all system tables (e.g. sysobjects, syscomments, sysprotects etc.) appear to be identical for both views. exec sp_helptext does not show the clause.
In MSSQL, sys.views has a with_check_option column, but there doesn't seem to be an equivalent for ASE.
In the example below, the WITH CHECK OPTION clause works exactly as expected, i.e. an insert into check_test_2 fails unless visible is set to Y. Is there any way to determine which of the views has the WITH CHECK OPTION clause set?
Example code:
use tempdb
go
if object_id('check_test') is not null drop table check_test
go
create table check_test (id int, visible char(1))
go
if object_id('check_test_1') is not null drop view check_test_1
go
create view check_test_1
as
select *
from check_test
where visible = 'Y'
go
if object_id('check_test_2') is not null drop view check_test_2
go
create view check_test_2
as
select *
from check_test
where visible = 'Y'
with check option
go
Looks like this was a bug in Sybase ASE from 12.5 up to 16.0, but may be released in certain service packs (16.0 SP01, 15.7 SP134 - not yet confirmed).
http://scn.sap.com/thread/3713912
The solution is to set some switches (200 = print tree before optimization, 3604 = print output to client), then select from the view, and search in the output for CHECKOPT, so for the example from the question:
use tempdb
go
set switch on 200
set switch on 3604
set noexec on
go
select *
from check_test_2
go
Somewhere in the (rather large) output will be:
... root2stat:(0x10000000 (BCP_LABELS, CHECKOPT)) ...