The "sysbojects" table is system tables that stored all database objects such as Table, Store Procedure, Function and etc.
In most cases, the two columns most useful to you will be sysobjects.name and sysobjects.xtype. The letter 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
FN: Scalar valued Function
TF and IF: Table valued Function
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 =
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 * FROM INFORMATION_SCHEMA.ROUTINES
ORDERBY ROUTINE_TYPE, ROUTINE_NAME
