Understanding the sysobjects table

Everything about your SQL Server database is stored in its system tables. I doubt that you spend a lot of time inspecting the system tables since you are too busy with your user tables. However, you may need to do something unusual once in a while, such as list all the triggers in your database. You could inspect your tables one by one, but that can become quite labor intensive if you have 500 tables.

This is where knowledge of the sysobjects table comes in handy. While it is not recommended that you update this table, you are certainly allowed to interrogate it.

In most cases, the two columns most useful to you will be sysobjects.name and sysobjects.xtype. The former lists the names of the objects in question, while the latter identifies the type of object, using the following codes:

• C: Check constraint
• D: Default constraint
• F: Foreign Key constraint
• L: Log
• P: Stored procedure
• PK: Primary Key constraint
• RF: Replication Filter stored procedure
• S: System table
• TR: Trigger
• U: User table
• UQ: Unique constraint
• V: View
• X: Extended stored procedure

In the case of triggers, three other columns that identify the type of trigger are of interest: deltrig, instrig, and uptrig.

You can list all the objects of any type of interest using the following:

SELECT * FROM sysobjects WHERE xtype = <type of interest>

In the special case of triggers, which are owned by their parent table, you might want to interrogate the database using a self-join, like this:

SELECT
      Sys2.[name] TableName,
      Sys1.[name] TriggerName,
      CASE
            WHEN Sys1.deltrig > 0 THEN'Delete'
            WHEN Sys1.instrig > 0 THEN'Insert'
            WHEN Sys1.updtrig > 0 THEN'Update'
      END'TriggerType'
FROM
      sysobjects Sys1 JOIN sysobjects Sys2 ON Sys1.parent_obj = Sys2.[id]
WHERE Sys1.xtype='TR'
ORDERBY TableName

In SQL Server 2005, the preferred technique is to use the system views. This approach will insulate your queries from any changes that Microsoft might choose to make to the system tables.

Here is a simple example, using the INFORMATION_SCHEMA_TABLES view:

SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE
FROMINFORMATION_SCHEMA.TABLES
ORDERBY TABLE_SCHEMA, TABLE_NAME

Run this query against the AdventureWorks database or any of your own databases to produce a quick list of the tables.

In order to illustrate the power of these schema queries, look at the following statement, which will list all functions and procedures within the selected database:

SELECT*FROMINFORMATION_SCHEMA.ROUTINES
ORDERBY ROUTINE_TYPE, ROUTINE_NAME

There are 20 such Information Schema views. If you need to document your database (and who doesn't) and cannot afford a commercial solution such as ones offered by Red Gate or Apex, then by using these views and a little experimentation, you can ask SQL Server to document itself.