Tuesday 5 June 2012

Use Sysobjects in SQL Server to Find Useful Database Information


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

No comments:

Post a Comment