Setup Database Mail in SQL Server 2012 Express

SQL Server 2012 Express still supports Database Mail (DB Mail), but it’s well hidden.
Step 1
One should enable Database mail on the server, before setting up the database mail profile and accounts, Either can be done by using Transact SQL to enable Database Mail. Run the following statement in the SQl Server Management Studio.

use master
go
sp_configure 'show advanced options',1
go
reconfigure with override
go
sp_configure 'Database Mail XPs',1
--go
--sp_configure 'SQL Mail XPs',0
go
reconfigure
go

Step 2
One can enable the Configuration Component Database account by using the sysmail_add_account procedure.
You’d execute the below query.

EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'TestMailAccount',
@description = 'Mail account for Database Mail',
@email_address = 'tanmaya@mydomain.com',
@display_name = 'MyAccount',
@username='tanmaya@mydomain.com',
@password='1qwe432',
@mailserver_name = 'mail.mydomain.com'

Step 3
Now one should create a Mail profile.
You’d execute the below query.

EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'TestMailProfile',
@description = 'Profile needed for database mail'

Step 4
Next will be the sysmail_add_profileaccount procedure, to include the Database Mail account which is created in step 2, along with the Database Mail profile in step 3.
You’d execute the below query.

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'TestMailProfile',
@account_name = 'TestMailAccount',
@sequence_number = 1

Step 5
You’d execute the below query.

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'TestMailProfile',
@principal_name = 'public',
@is_default = 1 ;

UPDATE msdb.dbo.sysmail_server SET enable_ssl=1 

Step 6
After all these settings done, try to send a test mail from MSSQL Server.
You’d execute the below query.

declare @body1 varchar(100)
set @body1 = 'Server :'+@@servername+ ' Test DB Email '
EXEC msdb.dbo.sp_send_dbmail @recipients='tanmaya@mydomain.com',
@subject = 'Test',
@body = @body1,
@body_format = 'HTML' ;

Step 7

You can review the logs linked to Database Mail.

You’d execute the below query.

SELECT * FROM msdb.dbo.sysmail_event_log

Listing all tables in a MSSQL Database and their row counts and space uses

Using sp_spaceused

sp_spaceused without parameters displays the disk space reserved and used by the whole database. However by specifying a table name as the first parameter it will display the number of rows, disk space used and reserved by a table. We can use this with the sp_MSForEachTable procedure mentioned above to get results for every table. An advantage to this approach is that it also shows the space used each table (data and index).

CREATE TABLE #RowCountsAndSizes (TableName NVARCHAR(128),rows CHAR(11),      
       reserved VARCHAR(18),data VARCHAR(18),index_size VARCHAR(18), 
       unused VARCHAR(18))

EXEC       sp_MSForEachTable 'INSERT INTO #RowCountsAndSizes EXEC sp_spaceused ''?'' '

SELECT     TableName,CONVERT(bigint,rows) AS NumberOfRows,
           CONVERT(bigint,left(reserved,len(reserved)-3)) AS SizeinKB
FROM       #RowCountsAndSizes 
ORDER BY   NumberOfRows DESC,SizeinKB DESC,TableName

DROP TABLE #RowCountsAndSizes

Hope the content is helpful.

Thanks
 

Generate Random Password in SQL Server Store Procedure

Sometimes there is a need to reset a password using a temporary password or generate a random password for a new user. This simple store procedure is creating it.

create proc [dbo].uspRandChars
    @len int,
    @min tinyint = 48,
    @range tinyint = 74,
    @exclude varchar(50) = '0:;<=>?@O[]`^\/',
    @output varchar(50) output
as 
    declare @char char
    set @output = ''
 
    while @len > 0 begin
       select @char = char(round(rand() * @range + @min, 0))
       if charindex(@char, @exclude) = 0 begin
           set @output += @char
           set @len = @len - 1
       end
    end
;
go

LEN - specifies the length of the result (required)
MIN - sets the starting ASCII code (optional: defaults to "48": which is the number zero)
RANGE - determines the range of how many ASCII characters to include (optional: defaults to "74"  (48 + 74 = 122) where 122 is a lowercase "z")
EXCLUDE - a string of characters to exclude from the final output (optional: defaults include zero, capital "O", and these punctuation marks :;<=>?@[]`^\/)

To use the stored procedure issue commands such as the following.

declare @newpwd varchar(20)


-- all values between ASCII code 48 - 122 excluding defaults
exec [dbo].uspRandChars @len=8, @output=@newpwd out
select @newpwd


-- all lower case letters excluding o and l
exec [dbo].uspRandChars @len=10, @min=97, @range=25, @exclude='ol', @output=@newpwd out
select @newpwd


-- all upper case letters excluding O
exec [dbo].uspRandChars @len=12, @min=65, @range=25, @exclude='O', @output=@newpwd out
select @newpwd


-- all numbers between 0 and 9
exec [dbo].uspRandChars @len=14, @min=48, @range=9, @exclude='', @output=@newpwd out
select @newpwd

Here is sample output from the above commands:





Reference : http://www.mssqltips.com/sqlservertip/2534/sql-server-stored-procedure-to-generate-random-passwords/