Summary
Each SQL Server Database has a set of system tables that are used to store data about the configuration of SQL Server and SQL Server Objects. The system tables have data about the following types of information: table names, column names, column data types, primary keys, indexes, foreign key relationships, transaction log records, database users, backups, restores, stored procedures, triggers, etc. Two of the more useful SQL Server System Tables are SysObjects and SysColumns. The best way to approach the system tables is to familairize yourself with them for a while by looking at the data that is in the tables and finding ways to write a query that returns only the data that you are interested in. Below are two queries that can be very useful.

SysObjects
Returns A List of the Tables in a SQL Server Database
SELECT SO.NAME FROM sysobjects SO WHERE SO.Name <> 'sysdiagrams' AND Left(SO.Name, 3) <> 'sp_' AND Left(SO.Name, 3) > 'fn_' AND Left(SO.Name, 6) <> 'queue_' AND xtype = 'U' ORDER BY SO.Name

SysColumns
Returns A List of the Columns in a SQL Server Table
SELECT SC.NAME, SC.xtype FROM sysobjects SO, syscolumns SC WHERE SO.ID = SC.ID AND SO.Name = '[Name_Of_Database_Table]' AND SO.Name <> 'sysdiagrams' AND Left(SO.Name, 3) <> 'sp_' AND Left(SO.Name, 3) <> 'fn_' AND Left(SO.Name, 6) <> 'queue_' AND SO.xtype = 'U' ORDER BY SO.Name, SC.ColOrder