Understanding the SQL Server System Table sysobjects

From: http://www.builder.com.cn/2006/1224/347745.shtml

 

All information about the SQL Server database is stored in its system table. I doubt whether you have spent a lot of time checking system tables, because you are always busy with user tables. However, you may need to do something unusual occasionally, such as all database triggers. You can check tables one by one, but if you have 500 tables, this may consume a considerable amount of labor.

This makes the sysobjects table useful. Although I do not recommend that you update this form, you certainly have the right to review it.

In most cases, the two most useful columns are sysobjects. Name and sysobjects. xtype. The previous one is used to list the names of objects to be evaluated, and the other one is used to define the object type. The Code is as follows:

• C:Check constraints.
• D:Default Constraints
• F:Foreign key constraint
• L:Logs
• P:Stored Procedure
• PK:Primary key constraint
• RF:Copy and filter stored procedures
• S:System Table
• Tr:Trigger
• U:Used for tables.
• Uq:Unique constraints.
• V:View
• X:Extended Stored Procedure

When a trigger is encountered, the other three columns used to identify the trigger type are deltrig, instrig, and uptrig.

You can use the following command to list all objects of interest:

Select * From sysobjects where xtype = <type of interest>

In special cases, when the parent table has a trigger, you may want to use the following code to find the database:

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 technology is to use the System View. This method isolates your query from any changes made to the system table selected by Microsoft.

The following 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

For the adventureworks database or any of your own databases, you can use this query to generate a quick table list.

To illustrate the query capabilities of these architectures, refer to the following statements to list all functions and stored procedures in the selected database.

Select * frominformation_schema.routines
Orderby routine_type, routine_name

There are 20 such information architecture views on the market. If you need to archive the database and cannot undertake commercial solutions, such as the solutions provided by red gate or apex, then through these views and a little experiment, you can have SQL Server generate documents for yourself.