Data Management

Understanding SQL Server's sysobjects table

Arthur Fuller explains why knowledge of the SQL Server sysobjects table can come in handy in certain situations. He also offers details about the two columns that will be most useful to you: sysobjects.name and sysobjects.xtype.

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.

Miss a tip?

Check out the SQL Server archive, and catch up on the most recent editions of Arthur Fuller's column.

TechRepublic's free SQL Server newsletter, delivered each Tuesday, contains hands-on tips that will help you become more adept with this powerful relational database management system. Automatically subscribe today!

3 comments
elaine_mei
elaine_mei

This is a very nice article and is very handy database administrators Thanks

gsquared
gsquared

I have a number of procs that pull data from sysobjects based on data in the "text" column of syscomments. That gives me the create script for procs, functions, triggers, etc., so I can, for example, find every proc that references server "SQL2" by: select name, xtype from sysobjects where id in (select id from syscomments where text like '%sql2%') and xtype = 'p' That way, when I'm moving copies of my current production database over to a development server, I can change the references to other servers so I don't end up updating data on a production system while testing my development database. (If, for example, I have a proc that puts an e-mail in the queue to go out to a customer based on a workflow event, I want that e-mail going to a virtual server that doesn't actually send the e-mail, when I'm running the proc on my dev system.) In that kind of case, where you have a separate live/production server, and a development server (and possibly even a test server), creating some setup scripts that look through syscomments and let you know everything you need to update to make sure you don't mess up the production system, is a very valuable extension to what you article mentions. (I actually have some setup scripts that will automatically update live code to a testable version on the dev server, and then list the objects that the script thinks need to be updated, but where I haven't provided the necessary specifics.)

chapman.tim
chapman.tim

You forgot to mention function types (FN, TF, IF).

Editor's Picks