"Fascinating!" - Mr Spock, Star Trek, The Original Series
The sysobjects table in SQL Server 2000 contains information about the objects in a database. For every constraint, default, role, log, rule, stored procedure etc, there is one row in the sysobjects table.
On of the column in this table is XTYPE. This is the code for the object type of the record. Unlike in Oracle's dictionary view DBA_OBJECTS, where the OBJECT_TYPE column is spelled out in full ('TABLE','VIEW' etc), SQL Server's XTYPE column is just a code, and while some of them may be obvious, others may not be so clear.
If you were to issue the following, you would get back a list of all the user tables in the database:
select * from sysobjects where xtype='U'So, presented below, is the list.
- C = CHECK Constraint
- D = Default or DEFAULT constraint
- F = FOREIGN KEY constraint
- L = Log
- P = Stored Procedure
- PK = PRIMARY KEY constraint (type is K)
- RF = Replication filter stored procedure
- S = System table
- TR = Trigger
- U = User Table
- UQ = UNIQUE constraint (type is K)
- V = View
- X = Extended stored procedure
The sysobjects table is documented here.
In SQL Server 2005 and onwards, the sysobjects view was replaced by sys.objects - this view contains a row for each user-defined, schema-scoped object that is created within a database.
The xtype column is no more - there is now a type column, that still contains the one or two character codes. However, there's also now a type_desc column which gives an associated description. For example, type F has a type_desc value of "FOREIGN_KEY_CONSTRAINT".
The sys.objects table is fully documented in Books Online.
