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 L – log N – partition condition P – Transact-SQL or SQLJ procedure 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-19)
systat3	unsigned smallint	Additional internal status information (see Table 1-20)
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 The 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

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

**Table 1-18: sysstat control bits in the sysobjects table**
Decimal	Hex	Object type	Description
0	0x0	O_ANY	Any illegal object
1	0x1	O_SYSTEM	System object
2	0x2	O_VIEW	View
3	0x3	O_USER	User object
4	0x4	O_PROC	Stored procedure
5	0x5	O_LOG	Log
6	0x6	O_DEFAULT	Default value spec
7	0x7	O_DOMAIN	Domain rule
8	0x8	O_TRIGGER	Trigger procedure
9	0x9	O_REFERENCE	Referential integrity constraint
10	A	O_CHECK	Check constraint
11	B	O_XTYPE	Extended type
12	C	O_FUNC	Stored function
		O_TYPE_MAX	O_FUNC Updates the maximum value of object type when you add a new one. Use O_TYPE_MAX in the print routines to print the string for the #define value for this field. Refer: useful/statbits.c and use of macro PRTYPESTR in print routines.
16	0x10	O_CLUST	Has clustered index
32	0x20	O_NONCLUST	Has nonclustered index
		OBJ_FOR_SYSDEPENDS(obj_type)	Checks whether the object needs entries in sysdepends when creating or dropping the following object type: O_PROC O_TRIGGER O_VIEW O_DEFAULT O_DOMAIN O_FUNC
64	0x40	O_LOGGED	The object is logged. The following bit is overloaded and has different meaning for tables and for stored procedures. This information is decoded in the print routine prOBJSTAT_OBJSYSSTAT().
64	0x40	O_PROC_SUBSCRIBABLE	The stored procedure is subscribable Replication Server Support The O_LOGGED bit is overloaded. In case the object is a stored procedure, O_PROC_SUBSCRIBABLE is used to denote whether or not the stored procedure is subscribable.
128	0x80	O_IN_CREATE	The object is being created
256	0x100	O_READONLY	The object contains suspect indexes and can only be used for read-only purposes until you have run dbcc reindex.
512	0x200	O_SUSPECT	The object flagged by recovery as possibly damaged; run dbcc. Checked by opentable.
1024	0x400	O_FAKE	The object is “fake”; that is, it resides in tempdb and is redefined for every query step that uses it
2048	0x800	O_EXTTABLE	The object is an external table, such as Stratus VOS files
4096	0x1000	O_RAMBOIX	Tags a system table that will have its index(es) re-created.
8192	0x2000	O_TEXTIMAGE	The object contains text/image fields
16384	0x4000	O_TABNOLOG	Unused
32768	0x8000	O_REPLICATED	The table or procedure is replicated

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

**Table 1-19: 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-20 lists the bit representations for the sysstat3 column:

**Table 1-20: sysstat3 control bits in the sysobjects table**
Decimal	Hex	Status
32768	0x8000	Table participates in incremental transfer

Indexes

Unique clustered index on id
Nonclustered index on name, uid