sysobjects

All databases

Description

sysobjects contains one row for each table, view, stored procedure, extended stored procedure, log, rule, default, trigger, check constraint, referential constraint, computed column, function-based index key, and (in tempdb only) temporary object, and other forms of compiled objects. It also contains one row for each partition condition ID when object type is N.

sysobjects has an entry for each key with type EK (encryption key).

For cross-database key references, syscolumns.encrdate matches sysobjects.crdate.

encrkeyid in sysencryptkeys matches the id column in sysobjects.

Columns

The columns for sysobjects are:

Name

Datatype

Description

name

varchar(255) not null

Object name.

id

int

Object ID.

uid

int

User ID of object owner.

type

char(2)

One of the following object types:

  • C – computed column

  • D – default

  • DD – decrypt default

  • F – SQLJ function

  • N – partition condition

  • P – Transact-SQL or SQLJ procedure

  • PP – the predicate of a privilege

  • PR – prepare objects (created by Dynamic SQL)

  • R – rule

  • RI – referential constraint

  • S – system table

  • TR – trigger

  • U – user table

  • V – view

  • XP – extended stored procedure.

userstat

smallint

Application-dependent type information (32768 decimal [0x8000 hex] indicates to Data Workbench® that a procedure is a report).

sysstat

smallint

Internal status information (256 decimal [0x100 hex] indicates that table is read-only)

indexdel

smallint

Recounts the changes in the schema of an object and and updates schemacnt.

schemacnt

smallint

Count of changes in the schema of an object (incremented if a rule or default is added)

sysstat2

int

Additional internal status information (see Table 1-18)

systat3

unsigned smallint

Additional internal status information (see Table 1-19)

crdate

datetime

Date the object was created

expdate

datetime

Reserved

deltrig

int

Stored procedure ID of a delete trigger if the entry is a table. Table ID if the entry is a trigger.

instrig

int

Stored procedure ID of a table’s insert trigger if the entry is a table

updtrig

int

Stored procedure ID of a table’s update trigger if the entry is a table

seltrig

int

Reserved

ckfirst

int

ID of first check constraint on the table

cache

smallint

Reserved

audflags

int null

Object’s audit settings

objspare

smallint

Spare

versionts

binary(6) null

The version timestamp of the last schema change for this object (used by Replication Server)

loginame

varchar(30) null

Login name of the user who created the object

identburnmax

numeric(17) null

Maximum burned value for identity column if any in this object

NoteThe identburnmax column is stored in an internal format. Use the identity_burn_max() function if you need a value.

spacestate

smallint null

For internal use only

erlchgts

binary(8) null

For internal use only

lobcomp_lvl

tinyint

LOB compression level

Table 1-17 lists the bit representations for the sysstat column:

Table 1-17: sysstat control bits in the sysobjects table

Decimal

Hex

Description

0

0x0

Any illegal object

1

0x1

System object

2

0x2

View

3

0x3

User object

4

0x4

Stored procedure

5

0x5

Predicate

6

0x6

Default value spec

7

0x7

Domain rule

8

0x8

Trigger procedure

9

0x9

Referential integrity constraint

10

0xA

Check constraint

11

0xB

Extended type

12

0xC

Stored function

13

0xD

Computed column

14

0xE

Partition condition

15

0xF

Encryption key

16

0x10

Has clustered index

32

0x20

Has nonclustered index

64

0x40

If the object is a table, changes to the object are logged. If the object is a procedure, indicates that replication can subscribe to executions of the procedure.

128

0x80

The object is being created

256

0x100

The object contains suspect indexes and can only be used for read-only purposes until you have run dbcc reindex.

512

0x200

The object flagged by recovery as possibly damaged; run dbcc. Checked by opentable.

1024

0x400

The object is “fake”; that is, it resides in tempdb and is redefined for every query step that uses it

2048

0x800

The object is an external table, such as Stratus VOS files

4096

0x1000

Tags a system table that will have its index(es) re-created.

8192

0x2000

The object contains text/image fields

16384

0x4000

Unused

32768

0x8000

The table or procedure is replicated

Table 1-18 lists the bit representations for the sysstat2 column:

Table 1-18: sysstat2 control bits in the sysobjects table

Decimal

Hex

Status

1

0x1

Table has a referential constraint.

2

0x2

Table has a foreign-key constraint.

4

0x4

Table has more than one check constraint.

8

0x8

Table has a primary-key constraint.

16

0x10

Stored procedure can execute only in chained transaction mode.

32

0x20

Stored procedure can execute in any transaction mode.

64

0x40

Table has an IDENTITY field.

128

0x80

Not used.

256

0x100

Not used.

512

0x200

Table does not contain variable-length columns.

1024

0x400

Table is remote.

2048

0x800

Table is a proxy table created with the existing keyword.

4096

0x1000

Not used.

8192

0x2000

Table uses allpages locking scheme.

16384

0x4000

Table uses datapages locking scheme.

32768

0x8000

Table uses datarows locking scheme.

65536

0x10000

Table was created in a version 11.9 or later server.

131072

0x20000

Table has a clustered index.

262144

0x40000

Object represents an Embedded SQL procedure.

524288

0x80000

Not used.

16777216

0x1000000

Object represents an access rule..

33554432

0x2000000

Object represents a SQLJ stored procedure.

67108864

0x4000000

Object represents an OR access rule.

1073741824

0x40000000

Table contains one or more function-based indexes.

2147483648

0x80000000

Object has an extended index

Table 1-19 lists the bit representations for the sysstat4 column:

Table 1-19: sysstat3 control bits in the sysobjects table

Decimal

Hex

Status

2048

0x0800

Table contains LOB compressed data

4096

0x1000

Table uses row-level compression

8192

0x2000

Table uses page-level compression

16384

0x4000

Table contains compressed data

32768

0x8000

Table participates in incremental transfer

Indexes