How to find locking schema for set of tables in database?

Question

There are some 20 tables in a database of Sybase instance, where i need to find the locking schema of tables. Once locking schema is found, i need to compare it in other database which also have same 20 tables. build-ASE16 SP2

Answer 1

Use the following to determine the locking schema for each user table in an ASE database:

SELECT TableName = CONVERT(VARCHAR(30), so.name)
    , LockScheme = CONVERT(VARCHAR(30), lockscheme(so.id))
FROM sysobjects so
WHERE so.type IN ('S', 'U');

The CONVERT(VARCHAR(30), xxx) is simply to make the output easier to see in a console session. If you are using a GUI tool to connect to the ASE instance, they are probably not necessary. Be aware that if you have table names longer than 30 characters, they will be truncated in the output.

Run that in both instances and simply compare the output in BeyondCompare, or some other differencing tool.

Answer 2

As suggested by Max, adding the answer that worked for me.

This is in ASE16 SP2 version,
To Check if database object has AllPages Lock schema

select name 'AllPages Lock Tables', sysstat2 from sysobjects where type = 'U' and sysstat2 & 8192 = 8192 and name = **"<obj name>"**

To Check if database object has DataPages Lock schema

select name 'DataPages Lock Tables', sysstat2 from sysobjects where type = 'U' and sysstat2 & 16384 = 16384 and name = **"<obj name>"**

To Check if database object has DataRows Lock schema

select name 'DataRows Lock Tables', sysstat2 from sysobjects where type = 'U' and sysstat2 & 32768 = 32768 and name = **"<obj name>"**