SYSTEM TABLES HELP IN SQL SERVER 2005

1.
SELECT * FROM sys.objects  
Info : Contains a row for each user-defined, schema-scoped object that is created within a database.
sys.objects does not show DDL triggers, because they are not schema-scoped.

2.
select * from sysobjects
SELECT * FROM sys.sysobjects      
Info: Contains one row for each object that is created within a database, such as a constraint, default, log, rule, and stored procedure.    
 
3.
SELECT * FROM sys.all_columns
Info : Shows the union of all columns belonging to user-defined objects and system objects. 

4.
SELECT * FROM sys.all_objects  
Info : Shows the UNION of all schema-scoped user-defined objects and system objects. 
 
5.
SELECT * FROM sys.all_parameters
Info : Shows the union of all parameters that belong to user-defined or system objects.

6.
SELECT * FROM sys.all_sql_modules
Info : Returns the union of sys.sql_modules and sys.system_sql_modules.

7.
SELECT * FROM sys.all_views
Info : Shows the UNION of all user-defined and system views.

8.Select * from sys.triggers
Info : All triggers, both DML and DDL, are found in sys.triggers. sys.triggers supports a mixture of name-scoping rules for the various kinds of triggers. 

9.
Select * from sys.types
Info : Contains a row for each system and user-defined type.

10.select * from sys.xml_schema_collections
Info : Returns a row per XML schema collection. An XML schema collection is a named set of XSD definitions.
The XML schema collection itself is contained in a relational schema, and it is identified by a schema-scoped Transact-SQL name.
The following tuples are unique: xml_collection_id, and schema_id and name.

Please go through MSDN for detailed explainations.

Pawan Kumar
pawankkmr@hotmail.com