SQL Server's system table sysobjects

All information about the SQL Server database is stored in its system table. I wonder if you've spent more time checking system forms because you're always busy with user forms. However, you may need to do something unusual occasionally, such as all triggers for a database. You can check the forms one by one, but if you have 500 of them, this can be quite a lot of labor.

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

In most cases, the two columns that are most useful to you are sysobjects.name and Sysobjects.xtype. The previous one used to list the names of the objects to be examined, and then a type to define the object, with the following code:

C: Check the constraint.

D: Default constraint

F: FOREIGN KEY constraints

L: Log

P: Stored Procedures

PK: PRIMARY KEY constraint

RF: Replication Filter Stored Procedures

S: System table

TR: Triggers

U: For tables.

UQ: a unique constraint.

V: View

X: Stored procedures that are extended

In the case of a trigger, 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, where the parent table has triggers, you might want to find the database in the following code:

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 view. This approach will isolate your query from any changes Microsoft has chosen to make to the system table.

The following is a simple example that uses the Information_schema_tables view:

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

For a AdventureWorks database or any database of your own, use this query to produce a quick list of tables.

To illustrate the capabilities of these schema queries, look at the following statements, which list all the functions and stored procedures in the selected database.

Select*frominformation_schema. Routines

by Routine_type, Routine_name

There are 20 such views of information architecture on the market. If you need to archive your database and you can't afford a business solution, such as Red Gate or apex, then with these views and a little experimentation, you can have SQL Server generate your own documentation.