Important MS SQL server system Views

Microsoft SQL server has a lot of built in system views which provides information about almost all of the objects in the database. Let's talk about the most important system views:

1)sys.databases
This view will return all the databases in the instance of a Sql Server.

Columns:
name ( datatype sysname ) of database is unique with in the instance of a SQL Server
database_id ( datatype int ) of database is unique with in the instance of a SQL Server
Database state ( type tinyint ) :

0 = ONLINE

1 = RESTORING

2 = RECOVERING

3 = RECOVERY_PENDING

4 = SUSPECT

5 = EMERGENCY

6 = OFFLINE

2) sys.objects

This view provides information about all of the objects in the selected database. 

The name ( datatype sysname ) is the name of the object.

The object_id ( datatype int ) is unique with in the database.

The type column ( datatype char(2) ) specifies whether it is a function, procedure or a table etc.

Important types are given below.

AF = Aggregate function (CLR)

C = CHECK constraint

D = DEFAULT (constraint or stand-alone)

F = FOREIGN KEY constraint

FN = SQL scalar function

FS = Assembly (CLR) scalar-function

FT = Assembly (CLR) table-valued function

IF = SQL inline table-valued function

IT = Internal table

P = SQL Stored Procedure

PC = Assembly (CLR) stored-procedure

PG = Plan guide

PK = PRIMARY KEY constraint

R = Rule (old-style, stand-alone)

RF = Replication-filter-procedure

S = System base table

SN = Synonym

SQ = Service queue

TA = Assembly (CLR) DML trigger

TF = SQL table-valued-function

TR = SQL DML trigger

TT = Table type

U = Table (user-defined)

UQ = UNIQUE constraint

V = View

X = Extended stored procedure

Example:

select * from sys.objects     
where type='V'
            OR
select * from sys.views
--Both queries will bring the same result.

3) sys.procedures

This procedure will return all the procedures from the selected database

3) sys.tables

This view will returns all the tables from the selected database

4) sys.views 

This view will return all the views from the selected database.

5) sys.triggers;

This view will return all the triggers from the selected database.

6) sys.columns
This will retrun all the columns of the objects ( Table,Views etc ) in the selected database

Source:
MSDN
Read more at

http://msdn.microsoft.com/en-us/library/ms178534.aspx

http://msdn.microsoft.com/en-us/library/ms189783.aspx