Optional Parameters in SQL Stored Procedures

You have an employees table that has the employee id, first name and last name. The table is defined as follows:

create table [dbo].[employees] (
  [id] int identity(1, 1) not null,
  [firstName] varchar(255) null,
  [lastName] varchar(255) null,
  primary key clustered ([id])
)
on [primary]
go

and insert with the following sample data:

INSERT INTO [dbo].[employees] ([firstName], [lastName])
VALUES (N'Ravi', N'Kumar'),
(N'Ravi', N'Kishore'),
(N'Mohan', N'Varma')
GO

You would like to write a stored procedure to return this data but if you don't want to write separate procedure for each type of employee search you want to execute. Ideally, you want to write one stored procedure that will return all employees or only an employee with a certain id or an employee has a certain first name (or last name for that matter). To get records use optional parameters in your stored procedure. You define your procedures as follows:

CREATE    PROC    dbo.spGetEmployees
    @employeeID int = null,
    @firstName varchar(255) = null,
    @lastName varchar(255) = null
as
BEGIN
SELECT    *
FROM   dbo.employees
WHERE (id = @employeeID or @employeeID is null)
      and
      (firstName = @firstName or @firstName is null)
      and
      (lastName = @lastName or @lastName is null)
END

Now you can call the same stored procedure 4 different ways:

-- Without parameters to get all the employees
EXEC dbo.spGetEmployees
-- With id parameter to get an employee with a specific id
EXEC dbo.spGetEmployees 1
-- With first name parameter to get an employee with a specific first name
EXEC dbo.spGetEmployees null, 'Ravi'
-- With last name parameter to get an employee with a specific last name
EXEC dbo.spGetEmployees null, null, 'Kishore'

Note: 

The parameter order is important. If you are specifying only the first optional parameter, you need not to do anything else. If you want to pass second and  then pass first parameter as null as shown above script.

Most of the times there are requirements when we need to send automatic notification emails to all or specific email address in the database based on some condition.

For example send birthday wishes to all customers whose birthday matches the current day.  Connect to the database engine of SQL server using SQL Server Management Studio.  Expand the SQL Server Agent. You will see a Jobs folder over there. Right click on jobs and choose NewJob as shown below screen.

 A New Job popup will appear. Specify the name of the job and description as shown below.

1.     Name - Name of the Job

2.     Description – Description of the Job (Optional)

3.     Enabled – Determines whether job is enabled or disabled

 

Select "Steps" option in the left menu of the New Job popup window. A SQL job can contain one or more steps. A step might be simply an SQL statement or a stored procedure call. Add you step here 

In the New Job Step Window enter the following details

1.     Step Name - Name of the Step

2.     Type  – Select Transact SQL Script

3.     Database – Select the database on which you want to run the script.

4.     Command – Paste the SQL Script which you wish the Job Scheduler to run 

And then click on OK button it will show New Job window.

 

Select "Schedules" option in the left menu of the New Job popup window.  In the New Schedule Window enter the following details

1.     Name - Name of the Step

2.     Enabled – Determines whether Schedule is enabled or disabled

3.     Schedule Type – Select Recurring schedule type since we need to run it daily

4.     Occurs – Daily since we need to run it daily

5.     Daily Frequency – Since we need to run once a day, select the time you wish to run

6.     Start date – Select date from when you want the schedule to run.

 Select OK button a new job is successfully created.  

You will see a new entry in the SQL Server agent -----> Jobs

To start the job right click the job and in the context menu click Start Job as shown bellow.

Stored Procedure Script:

Note: 

1.  Add database name and schema to the table.

2.  Add database name and schema to the sending email  

            (EXEC msdb.dbo.sp_send_dbmail
            @profile_name = 'EmailSending',
            @recipients = @EmpMaild,
            @copy_recipients = @MailID,
            @body=@body,
            @subject = 'Scheduled Request Will be exprie')

DECLARE
@out_desc VARCHAR(1000),
@out_mesg VARCHAR(10)

DECLARE @name VARCHAR(20),
@birthdate datetime,
@email NVARCHAR(50)

DECLARE @body NVARCHAR(1000)

DECLARE C1 CURSOR READ_ONLY
FOR
SELECT [name], [birthdate], [email]
FROM Customers
OPEN C1
FETCH NEXT FROM C1 INTO
@name, @birthdate, @email
WHILE @@FETCH_STATUS = 0
BEGIN
      IF DATEPART(DAY,@birthdate) = DATEPART(DAY,GETDATE())
      AND DATEPART(MONTH,@birthdate) = DATEPART(MONTH,GETDATE())
      BEGIN
            SET @body = '<b>Happy Birthday ' + @name +
            '</b><br />Many happy returns of the day'
            + '<br /><br />Customer Relationship Department'
            EXEC sp_send_mail  'sender@abc.com',
            'xxxxxxx',
            @email,
            'Birthday Wishes',
            @body,
            'htmlbody',
            @output_mesg = @out_mesg output,
            @output_desc = @out_desc output
            PRINT @out_mesg
            PRINT @out_desc
      END
      FETCH NEXT FROM C1 INTO
      @name, @birthdate, @email
END
CLOSE C1
DEALLOCATE C1

Remove Row Editing Limitation in SQL Server 2008

In SQL Server 2008 Management Studio Express , we can change the default settings that allows to edit more than the 200 rows at a time, or select more than 1000 rows.
To modify the “Edit Top 200 Rows” or “Select Top 1000 Rows” settings do the following steps.

Step 1:

Run the SQL Management Studio Express 2008 and expand the database and then Tables select the table you can find  option to select Top 1000 Rows and Edit Top 200 Rows as shown bellow.  To change those follow below the steps.

  

Step 2:

Click the Tools -> Options from the menu bar and then you will find Options window.  Form that window Select SQL Server Object Explorer . Now you should be able to see the options as shown in the below window.

Change the Values

*Value for Edit Top Rows Command
*Value for Select Top Rows Command
Give the Values 0 here to select/ Edit all the Records as shown bellow and then click on OK button.

Now you can see the Edit All and Select All options on the table.

Identify Objects Type using sys.SysObjects

MS SQL Server stores the information about the objects (tables, views, functions, etc) stored in the database in a table, which is accessible by selecting from the sysobjects view (sql2000) or the sys.objects and sys.sysobjects views (sql2005, sql2008).

Object Type Abbreviation

1. AF = Aggregate function (CLR)
2. C = CHECK constraint
3. D = Default or DEFAULT constraint
4. F = FOREIGN KEY constraint
5. L = Log
6. FN = Scalar function
7. FS = Assembly (CLR) scalar-function
8. FT = Assembly (CLR) table-valued function
9. IF = In-lined table-function
10. IT = Internal table
11. P = Stored procedure
12. PC = Assembly (CLR) stored-procedure
13. PK = PRIMARY KEY constraint (type is K)
14. RF = Replication filter stored procedure
15. S = System table
16. SN = Synonym
17. SQ = Service queue
18. TA = Assembly (CLR) DML trigger
19. TF = Table function
20. TR = SQL DML Trigger
21. TT = Table type
22. U = User table
23. UQ = UNIQUE constraint (type is K)
24. V = View
25. X = Extended stored procedure

You can query the sys.SysObjects to get all the objects created with in the database.

Examples

1.  To Get Tables list.

SELECT *
FROM   sys.sysobjects
WHERE  TYPE = 'u'

2.  To Get Stored Procedures list.  

SELECT *
FROM   sys.sysobjects
WHERE  TYPE = 'p'

How do I get uppercase values returned only.

I have a requirement to get only upper case letters form the table.

DECLARE @CountryNames TABLE (Name VARCHAR(20))

INSERT INTO @CountryNames VALUES ('US')
INSERT INTO @CountryNames VALUES ('India')
INSERT INTO @CountryNames VALUES ('uk')
INSERT INTO @CountryNames VALUES ('AU')

Get all the records

SELECT  *  FROM  @CountryNames 

 

Get records which are starting the Capital letter

SELECT  *  FROM  @CountryNames   

WHERE  ASCII(Name) = (ASCII(UPPER(Name)))

Get Records total word is captial

SELECT     Name AS Country
FROM         @CountryNames
WHERE     (Name COLLATE Finnish_Swedish_CS_AS = UPPER(Name) COLLATE Finnish_Swedish_CS_AS)

T-SQL to Convert Rows into single Column

Many times we need to convert column values into rows. There are two scenarios here. First one is to concatenate all the values in column. And second one is to concatenate all the values in column along with another grouping column.

Consider you have below table

CREATE TABLE [dbo].[Tbl_Employee]

   (

     Id INT,

     Name VARCHAR(255)

   )

   GO

Insert some data in above table.

INSERT INTO [dbo].[Tbl_Employee ] 

      (Id, Name)

   SELECT 1, 'Amit' UNION ALL   

   SELECT 1, 'Bhanu' UNION ALL

   SELECT 1, 'Chandu' UNION ALL

   SELECT 1, 'Dhanush' UNION ALL

   SELECT 2, 'Ellesh' UNION ALL

   SELECT 2, 'Fair' UNION ALL

   SELECT 2, 'Govind' UNION ALL

   SELECT 3, 'Header' UNION ALL

   SELECT 3, 'Indu' UNION ALL

   SELECT 4, 'Janu' 

   GO

So you have following data in [dbo].[MyTable]

SELECT *  FROM [Tbl_Employee]

First – Concatenate all the values in column “Name”

   DECLARE @StrSQL VARCHAR(8000)

   SELECT @StrSQL = COALESCE(@StrSQL + ',', '') + Name

   FROM [dbo].[Tbl_Employee ]

   SELECT @StrSQL

   GO

   Name 
   ------------------------
   Amit,Bhanu,Chandu,Dhanush,Ellesh,Fair,Govind,Header,Indu,Janu

Second – Concatenate all the values in column “Name” grouping by column “Id”

   SELECT  

      Id,

      STUFF( (

                  SELECT

                  ',' + Name AS 'text()'

                  FROM [dbo].[Tbl_Employee ] t2

                  WHERE t2.id = t1.id

                  FOR XML PATH('')

               )

               ,1

               ,1

               ,''

             ) AS Name

   FROM [dbo].[Tbl_Employee ] t1

   GROUP BY Id

   GO

Database Mail vs SQL Mail in SQL Server

SQL Mail was what one would use to send emails from SQL Server in the SQL Server 2000 version. But in SQL Server 2005, SQL Mail is considered legacy. Microsoft introduced a better email component called Database Mail for SQL Server 2005.

Database mail is newly introduced concept in SQL Server 2005 and it is replacement of SQLMail of SQL Server earlier version. Database Mail has many enhancement over SQLMail. Database Mail is based on SMTP (Simple Mail Transfer Protocol) and also very fast and reliable where as SQLMail is based on MAPI (Messaging Application Programming Interface). Database mail depends on Service Broker so this service must be enabled for Database Mail. Database Mail can be encrypted for additional security. SQLMail is lesser secure as it can encrypt the message as well anybody can use SMTP to send email. Additionally, for MAPI to be enabled for SQLMail it will require Outlook to be installed. All this leads to potential security threat to database server.

why did Microsoft make the decision to move away from SQL Mail and introduce Database Mail?

1. SQL Mail was developed and runs in-process to SQL Server service. This means if SQL Mail goes down, your SQL Server also goes down. You don’t want this to happen on your production database because you will not have many happy users if that happens.
2. To use SQL Mail you need to install the extended MAPI client component on your server. How I usually do this is by installing Microsoft Outlook on the server. This will install the MAPI client component. Without MAPI, SQL Mail will not run.

What are the advantages of using Database Mail?

1. Unlike SQL Mail, Database Mail runs outside SQL Server in a separate process. So if Database Mail goes down, it will not bring down SQL Server.
2. Database Mail does not need Extended MAPI client components installed for it to run.
3. Database Mail can be configued with multiple SMTP accounts and with multiple profiles. This increases the robustness of the system. In a realtime environment, if one mail server goes down, Database Mail will still be able to send emails through the other configured SMTP accounts.
4. In Database Mail, all mail events are logged and you can view the mail history.
5. You can configure Database Mail to limit file sizes, disallowing large attachments and filtering of file extensions such as .exe or .bat

Where do you enable Database Mail and SQL Mail in SQL Server 2005?

Step1:  You do it through SQL Server 2005 Surface Area Configuration shown below. Click  "SQL Server Surface Area Configuration"

 Step2:  Click on Surface Area Configuration for Features as shown bellow screen.

 

Step 3:  Tick Enable Database Mail Stored Procedures to enable Database Mail in SQL Server and then click on OK button.

 

Step 4: Tick Enable SQL Mail Stored Procedures to enable SQL Mail in SQL Server and then click on OK button.

 Access Database Mail and SQL Mail and setup both in SQL Server Management Console, shown below.

Once that is setup, you can invoke Database Mail and SQL Mail to send emails and notification.

Database Mail:

 SQL Mail:

Where do you enable Database Mail and SQL Mail in SQL Server2008 ?

• Connect to the SQL Server Management Studio.
• In the Object Explorer, Go to root not i.e SQL Server node and Right-Click on it as shown below and point to the "Facets".

Click on Facets then it will show View Facets window as shown below.  Form Facet list select Surface Area Configuration.

Then it will display Surface area configuration for features of the Database Engine as shown below screen and the select DatabaseMailEnabled option and select true as shown in the below screen to enable Database email sending option and then click on OK button.

To enable SQL Email option follow the steps as shown below screen.

In summary, I suggest if you are using SQLMail, it is right time to upgrade to Database Mail.

If you still want to use SQLMail you will have to enable it with specific commands.

EXEC sp_configure 'SQL Mail XPs', 1
GO
RECONFIGURE
GO

 

Manage Profile Security(Database Mail)

Profiles are either public or private. A private profile is accessible only to specific users or roles. A public profile allows any user or role with access to the mail host database (msdb) to send e-mail using that profile.

A profile may be a default profile. In this case, users or roles can send e-mail using the profile without explicitly specifying the profile. If the user or role sending the e-mail message has a default private profile, Database Mail uses that profile. If the user or role has no default private profile, sp_send_dbmail uses the default public profile for the msdb database. If there is no default private profile for the user or role and no default public profile for the database, sp_send_dbmail returns an error. Only one profile can be marked as the default profile. To configure follow the bellow steps.

• Connect to the SQL Server Management Studio.
• In the Object Explorer, Go to Management Node,  expand Management Node, and Point to Database Mail option  and Right-Click on it as shown in the figure below:

Once Configure Database Mail option is clicked, Database Mail Configuration Wizard  appears.  This wizard helps to set up the database mail in SQL Server Instance as shown below.

 click on Next button shown above then it will show set of Set up options as shown in the figure below.

click on next button from the above screen then it will show Mange Profile Security window.  From the Manage Profile Security screen, select the target profile, then click in the Default Profile tab to activate the drop-down. Select Yes fromt the drop-down then click on next button as shown below screen.

Click on next button then it will show complete wizard window as shown below screen. From the Complete the Wizard screen, click on

Click on finish button then it will show status window as shown below.

Manage Profiles and Accounts (Database Mail)

Manage an existing Database Mail profile. A Database Mail profile is a collection of Database Mail accounts. Profiles improve reliability in cases where an e-mail server becomes unreachable, by providing alternative Database Mail accounts. At least one Database Mail account is required.  We can create new account ,new profile and we can view, change and delete existing account and profile. To do this follow the below steps.

• Connect to the SQL Server Management Studio.
• In the Object Explorer, Go to Management Node,  expand Management Node, and Point to Database Mail option  and Right-Click on it as shown in the figure below:

• Once Configure Database Mail option is clicked, Database Mail Configuration Wizard  appears.  This wizard helps to set up the database mail in SQL Server Instance as shown below.

 

when Next button is clicked as shown above then it will show Set up option as shown in the figure below.  In this we can find 4 options 

1. In this first option, new profile and account can be created, SMTP account will be added, security in the mail profile can be set up and system parameters can be configured.  To configure this follow the below link
2. In the second option Manage Database Mail Accounts and  Profiles. Here we can create,view and delete the accounts and profiles.
3. In third option Manage Profile Security.
4. View or Change System parameters.

For the first step you can find here Configure Database Mail – Send Email From SQL Database Now we will know about second option i.e Manage Database Mail Accounts and  Profiles configuration follow the below steps. check the radio button Mange Database Mail Accounts and Profiles option form the Database Mail Configuration Wizard window as shown below and then click on next button.

 

When click on next button then it will show Manage profiles and accounts window as shown below.  Here you can find 4 options, 

1. Create a new account (you can find how to configure a new account link )
2. View,Change or Delete an existing account
3. Create a new profile (you can find how to configure a new Profile link )
4. View,Change or Delete an existing profile.  

Here i will describe about 2 and 4th options.

View,Change or Delete an existing account
To View,Change or Delete an existing account check the radio button and click on next button as shown below. 

When click on next button it will show Mange existing account window. Here you can change/edit or you can delete the accounts.  If more than one account has been created then select the account form the account name drop down list and the select delete button to delete of change the email address and passwords and then click on next button as shown in the below screen.

When your deleting any account which is associated with any one of the profile then it throw an error as shown below screen shot. 

If more than one account then follow the steps as shown in the below screen shot and then click on next button.

 When click on delete button and then account will be deleted form the list and click on next button it will show deleted accounts list as shown in the below screen shot.

Click on finish button then it will show successful message and deleted list or updated list as shown in the below screen shot.

View,Change or Delete an existing Profile
To View,Change or Delete an existing profile check the radio button and click on next button as shown below.

 It will show database mail configuration window as shown below.  We can delete profile.  If more than one profile then select the deleted one and then click on delete button as shown bellow. here either we can add new SMTP mail account or remove the existing account. 

When you click on add button it will show a window to add a mail account to the profile as shown below window.

When you click on new account then it will show a window to create a new database mail account as shown below screen shot.  To configure follow the same steps as shown in the previous article.

after configuring or modifying profile click on next button then it will show complete wizard window as shown below.

Click on finish button then it will show status of the configuration as shown below.

SQL SERVER – 2008 – Configure Database Mail – Send Email From SQL Database

Introduction:  

SQL Server 2008 Database Mail feature within SQL Server Instance. Database mail was introduced in SQL server 2005 and it was a new feature that Microsoft has added in SQL Server 2005. Database mail is also available in SQL Server 2008. I did not find any difference in Database mail in 2005 and in 2008. Database mail in SQL Server 2008 is an enterprise solution that is used to send emails from Database Engine component of SQL Server. Basic function of SQL Server 2008 Database Mail is to send alert messages to database administrators with issues related to performance, disk space, SQL Jobs, Backup plans, and other changes in the database schema. This is a very neat feature that helps database administrators to keep track of all activities in SQL Server Instances across all servers in the network. Database mail has better performance, easy to set up and more reliable than SQL Mail in SQL Server 2000.  SQL Mail uses MAPI where as Database Mail uses SMTP. Another main point to remember on database mail is, it uses service broker service and this service need to be enabled in order to use Database Mail in SQL Server 2008. By default, database mail will not be enabled; there are different ways to configure it. We can enable it using system stored procedure, configuration manager or from the Database Mail Wizard during the set up.

Note: Database mail feature in SQL Server 2008 is not available in Express Edition.

What is Database Mail?

Database Mail is a mail queuing system. The email messages are stored in a queue within the msdb database waiting to be processed. When an email message is placed in the queue, an external process is triggered to send the email messages in the queue to the appropriate mail server. Once the email has been sent an email message with the status of the delivery is then posted back to SQL Server.

In order to send mail using Database Mail in SQL Server, there are 3 basic steps that need to be carried out. 1) Create Profile and Account 2) Configure Email 3) Send Email.

Step 1:  Create Profile and Account

First step in Database Mail set up is creating a profile. Mail profile is the main element of Database Mail.  A profile can have multiple email accounts added in that profile. Profile can be of two types, and they are:

Public Profile: A public profile is a mail profile set up in the database mail which can be accessed by any users and these users will have the ability to send emails.

Private Profile:  A private profile is that profile in which users who are granted access to this profile can use to send emails.

• Connect to the SQL Server Management Studio.
• In the Object Explorer, Go to Management Node,  expand Management Node, and Point to Database Mail option  and Right-Click on it as shown in the figure below:  

 

•  Once Configure Database Mail option is clicked, Database Mail Configuration Wizard  appears.  This wizard helps to set up the database mail in SQL Server Instance as shown below.

 

• Select Configuration Task window comes when Next button is clicked as shown above. Since i am setting up Database Mail for the first time therefore am selecting the Set up option as shown in the figure below.  In this set step, new profile can be created, SMTP account will be added, security in the mail profile can be set up and system parameters can be configured.  Click Next after select the set up option. 

Note:  In case, if Database Mail is already setup, we can choose to manage database mail and changing system parameters.

When you click Next, Following window comes since Database Mail is not enable on my instance so am going to enable it from here instead of using system stored procedure or configuration manager as I mentioned earlier.

Click Yes as marked in Red to enable the Database Mail and the next comes a window where we can create New Profile for the Database mail and then add new SMTP accounts for this profile.  Profile name is used by different users to send out email notifications.  As shown in the figure below, Profile name and Description of the Profile are created. The name of the Profile that I used is:  SQLServer2008 Database Mail Profile. Description is an optional but it is good to add description about the profile. I have added a short description as shown in the figure.

After profile name and description of the profile is added, we can add the SMTP Accounts by Clicking on Add…. button as shown in New Database Mail Account Window figure below.

Here provide the details like Account name is Birthday Mail, Description is an optional.  I have used my Gmail account as an Outgoing SMTP.
Under Outgoing Mail Server SMTP:

E-Mail Address:   abc@gmail.com
Display Name: Enter Display Name in Email
Reply Email:  It can be blank or we can use same email as above.
Server Name:  smtp.gmail. com, this is SMTP server.
Port Number:   default port number is 25 but Gmail smtp server port number to be used is 587 .
Secure Connection: We have to select SSL connection as shown in the picture for gmail.
Basic Authentication:  Provide gmail account and password for this account.
and then Click OK  to go back to New Profile window as shown below screen shot. Now, SMTP account details are added in the profile.  Click Next to  Go to Manage Security Window as shown below.

In this profile security management window, there are two options for selecting profiles which users are going to have access. They are public and private as shown in the below screen shot. Public profile can be accessed by all users, whereas private profile is accessed by only specific users.  I have used Public profile where I have chosen Public profile. Also have to make this profile default, so have to select Yes option from the drop down menu from Default profile as shown in the below screen shot.

We can configure more than so select which profile you wants to use it and then click on next button.

 Click Next button to go to Configure System Parameters window as shown below.

 In this window, we can see the information about how many times we can retry to get the mail, how long to wait when we retry the mail, maximum email size, what are the prohibited attachment extensions, Minimum life time for Database Mail and the logging level.  Default prohibited attachment extensions are: exec, dll, vbs, and js.  I have added two more extensions that I do not want to get the attachment from and they are; com and bat, which are shown in the picture above. 
To add more prohibited attachment file extensions, click on … on the right side of Configure System Parameters Window on Prohibited Attachment file Extensions under Systems Parameters column which is shown in screen shot.  Once you add more extension values, Click OK to go back to Configure System Parameters Window as shown below.  There are three levels of logging namely normal, verbose and extended.
I am using extended logging level as is the default one.

Click Next to go to the Confirmation Window for Database Mail Set up and is shown below.  Click Finish to complete the Database Mail set up steps.

Once finished button is clicked following Configuring window comes where we can see the Action, status and messages. If there are errors, there will be error messages.  Click Close button to close the Database Mail Configuration Wizard.

Step 2) Configure Email:

After the Account and the Profile are created successfully, we need to configure the Database Mail. To configure it, we need to enable the Database Mail XPs parameter through the sp_configure stored procedure, as shown here:

sp_CONFIGURE 'show advanced', 1
GO
RECONFIGURE
GO
sp_CONFIGURE 'Database Mail XPs', 1
GO
RECONFIGURE
GO

Step 3) Send Email:
To send the test email, go to Database Mail as shown in Database Mail Folder Screen shot Right click on Database Mail folder and the Click on Test Email … as shown in figure below.

 After Send Test E-Mail is clicked, following screen comes where Database Mail Profile, to which email address this message will be sent, subject of the message and body of the test email. I am using my email address,abc@gmail.com where the email will be sent. Click on Send Test E-Mail and this email will go to  Gmail inbox and check the mail.

 There is another way to test the sending mails after all configurations are done, we are now ready to send an email. To send mail, we need to execute a stored procedure sp_send_dbmail and provide the required parameters as shown below:

USE msdb
GO
EXEC sp_send_dbmail @profile_name='BirthdayEmailSending',
@recipients='abc@gmail.com',
@subject='Birthday message',
@body='This is the body of the test message.
Many many happy returns of the day...'

After all validations of the parameters entered are done, certain stored procedures are executed and the mail is queued by Service Broker 

Database Mail keeps copies of outgoing e-mail messages and displays them in the sysmail_allitems, sysmail_sentitems, sysmail_unsentitems, sysmail_faileditems . The status of the mail sent can be seen in sysmail_mailitems table, when the mail is sent successfully the sent_status field of the sysmail_mailitems table is set to 1 which can again be seen in sysmail_sentitems table. The mails that are failed will have the sent_status field  value to 2 and those are unsent will have value 3.
The log can be checked in sysmail_log table as shown below: 

SELECT *
FROM sysmail_mailitems
GO
SELECT *
FROM sysmail_log
GO

After sending mail you can check the mail received in your inbox.

Reference Links: 

• http://blog.sqlauthority.com/2008/08/23/sql-server-2008-configure-database-mail-send-email-from-sql-database/
• http://sql-articles.com/articles/dba/database-mail/
• http://www.sqlservercentral.com/blogs/databaseexpertisecom/2010/05/12/database-mail-set-up-in-sql-server-2008/