How to get identity, computed properties of a column in a table

Problem: How to get identity, computed properties of a column in a table?

Solution: COLUMNPROPERTY, starting from SQL server 2008 or higher.

Syntax: COLUMNPROPERTY (id , column , property )   

(Needs input) ID – OBJECT_ID of a table or a procedure.

Column – Column name or parameter name

(Needs Input) Property – Type of information you want to retrieve from the column.

This is a metadata function which spits out an output based on the property you want to read for a specific column or parameter in a procedure.

Example:

Example 1: In this example I would like to see all the information of identity columns in a database.

SELECT c.NAME as IdentityColumn,

                OBJECT_NAME(c.OBJECT_ID) AS tablename

FROM sys.columns c

INNER JOIN sys.tables t

                ON t.object_id = c.object_id

WHERE COLUMNPROPERTY(c.OBJECT_ID, c.NAME, 'IsIdentity') = 1

                AND t.is_ms_shipped = 0

OUTPUT:

IdentityColumn	tablename
EmployeeID	Employees
AuditCallID	AuditCall
CallId	Request
AddressPKey	Address
SubjectId	Subject

Example 2:

In this article I am going to show you all the columns which are full text indexed.

               

SELECT c.NAME as FullTextIndexedColumn,

                OBJECT_NAME(c.OBJECT_ID) AS tablename

FROM sys.columns c

INNER JOIN sys.tables t

                ON t.object_id = c.object_id

WHERE COLUMNPROPERTY(c.OBJECT_ID, c.NAME, 'IsFulltextIndexed') = 1

                AND t.is_ms_shipped = 0

OUTPUT:

FullText IndexedColumn	tablename
AddressLine1	Address
SubjectDescription	Subject

Read More

How to implement SOUNDEX

Problem: How to compare two words and get the difference of the string text?

Solution: 

SOUNDEX

This is a string function which gives out a four-character output based on the input string.

Based on the sounding of a name, address, or an object this will assign a value and similar pronunciations get the same result.

The first character of the output is always the first word of the input with Capital case and the rest three characters are based on the sounding of how they are pronounced.

DIFFERENCE

The next thing we can do is to compare the input; it can be compared using difference function to show how SOUNDEX values of two characters looks like.

Returns an integer value as output, lowest is 4, which means it is close to the sound of the input and compared value and 0 is the highest and which means they don’t sound similar.

Example:

Example 1: In this example I would like to show you how SOUNDEX works on two names of the same pronounce and spell differently.

SELECT [Fname]

      ,SOUNDEX([Fname]) as sounding

      ,[Lname]

      ,[Gender]

    FROM [dbo].[Subject]

OUTPUT:

Fname	sounding	Lname	Gender
Steven	S315	paul	male
Stephen	S315	xill	male

As you can see Steven and Stephen are spelled differently but the SOUNDEX values are same.

Example 2:

In this example I would like to show to compare the soundex codes of different words

SELECT [Fname]

      ,DIFFERENCE([Fname],LNAME) as sounding

      ,[Lname]

      ,[Gender]

    FROM [dbo].[Subject]

The lowest is 4, which means it is close to the sound of the input and compared value and 0 is the highest and which means they don’t sound similar.

OUTPUT:

Fname	sounding	Lname	Gender
Steven	0	paul	male
Stephen	4	Stephan	male

So as we can see the first name last name of Steven and paul doesn’t sound similar the difference is 0 and the Second row has an output of 4 which means they are sounding same almost.

Read More

Sent Status and mail log of the database mail.

Problem: How to know the status, content and log of the database mail?

Solution: 

In the last post as we were able to send emails for a SQL Server Agent job history, and for alerting when a job fails. As there is lot of activity going on the Database mail, how do we know whether any email went through or not, or the content of the mail, recipients and other information.

Each mail item sent from database mail gets an entry inside system tables of database mail tables. For the ease of the usage Microsoft has some system views created on this tables to retrieve the data sets.  

All these database mail views and tables can help us to track the information and activity of Mail server, account, profile and other details.

Example:

Example 1: In this example I would like to see all the information of unsent items.

USE msdb

GO

SELECT sml.description as mail_description,

smm.recipients,

smm.subject,

smat.attachment,

smat.filename,

smatt.transfer_id

 FROM

[dbo].[sysmail_log] sml

INNER JOIN

[dbo].[sysmail_mailitems] smm

ON smm.mailitem_id=sml.mailitem_id

LEFT JOIN

[dbo].[sysmail_attachments] smat

ON smm.mailitem_id=smat.mailitem_id

LEFT JOIN [dbo].[sysmail_attachments_transfer] smatt

ON smat.attachment=smatt.attachment

and smm.sent_status=0

OUTPUT:

mail_description	recipients	subject	attachment	filename	transfer_id
The mail could not be sent to the recipients	sqldevmahesh@gmail.com	Database Mail Test	NULL	NULL	NULL
The mail could not be sent to the recipients	sqldevmahesh@gmail.com	Database Mail Test	NULL	NULL	NULL
The mail could not be sent to the recipients	sqldevmahesh@gmail.com	Database Mail Test	NULL	NULL	NULL
The mail could not be sent to the recipients	sqldevmahesh@gmail.com	Database Mail Test	NULL	NULL	NULL

Example 2:

In this example I am going to show you to determine the server type of an email account and whether it’s an incoming server or outgoing server.

USE msdb

GO

SELECT

                sma.name as account_name,

                smp.name as profile_name,

                sms.servertype,

                sms.servername,

                sms.port,

                CASE

                                WHEN smsty.is_outgoing = 1

                                                THEN 'OUT-GOING SERVER'

                                WHEN is_incoming = 1

                                                THEN 'IN-COMING SERVER'

                                END [i/o]

FROM [dbo].[sysmail_account] sma

INNER JOIN [dbo].[sysmail_profileaccount] smpa

ON sma.account_id=smpa.account_id

INNER JOIN [dbo].[sysmail_profile] smp

ON smpa.profile_id=smp.profile_id

INNER JOIN

 [dbo].[sysmail_server] sms ON sma.account_id=smpa.account_id

INNER JOIN [dbo].[sysmail_servertype] smsty

                ON SMs.servertype = smsty.servertype

OUTPUT:

account_name	profile_name	servertype	servername	port	i/o
DBMail	DB Mail Public Profile	SMTP	0.0.0.0	25	OUT-GOING SERVER

 Please go through this article Configure Mail profile, on how to configure a mail account, profile.

Please go through this article send email from Database, on how to send a mail.

Read More

Sending an email from Database engine using database mail profile.

Problem: How to send an email from Database Mail.?

Solution: sp_send_dbmail

If we need to send alerts, notifications, Job status of a SQL Server agent from SQL Server Database. You may have to configure Database Mail to send e-mail message to concerned persons as it keeps notifying when there is a fatal error or failure of a SQL job. So, it keeps you aware of the process even though you are not on the computer.

After the configuration we can send an email using sp_send_dbmail.

This system procedure has few parameters that needs to passed in order to send an email to a particular person.

You can pass file attachments, query results and etc.

Example:

Example 1: In this example I would like to send a test email to a specified recipient using a profile that already exists.

EXEC msdb.dbo.sp_send_dbmail

                @recipients='mahesh.sambu@gmail.com', 

                @profile_name='DB_Mail Public Profile',

                @subject = 'email sent from database engine’, 

                @body = 'This email is sent in order to test the email server' ; 

Example 2:

In this article I am going to show you we can send an email to send a status update of a SQL job as a table format in HTML.

DECLARE @tableHTML  NVARCHAR(MAX) ; 

DECLARE @job_id UNIQUEIDENTIFIER,

                @jobname SYSNAME = 'syspolicy_purge_history'

 

SELECT @job_id = job_id

FROM msdb.dbo.sysjobs

WHERE [name] = @JobName

SET @tableHTML = 

    N'<H1>Job History Report</H1>' + 

    N'<table border="1">' + 

    N'<tr><th>Job_name</th><th>Step_name</th>' + 

    N'<th>Jobfailedatstep</th><th>rundate</th><th>runtime</th>' + 

    N'<th>runduration</th><th>logoutput</th></tr>' + 

    CAST ( (

SELECT td=sj.name , '',

td =        JS.step_name,'',

                td='Step ' + CAST(JH.step_id AS CHAR(3)) + ' of ' + CAST(MAX(js.step_id) OVER (ORDER BY (SELECT 0)) as char(3)) ,'',

                td =CONVERT(date, left(jh.run_date, 8)), '',

                td =CAST(dateadd(hour, (jh.run_time / 10000) % 100, dateadd(minute, (jh.run_time / 100) % 100, dateadd(second, (jh.run_time / 1) % 100, cast('00:00:00' AS TIME(2)))))AS char(8)), '',

                td =JH.run_duration ,'',

                td =CASE                              WHEN JSL.[log] IS NULL                                 THEN JH.[Message]

                                                                                ELSE JSL.[log]                     END,''

FROM msdb.dbo.sysjobs sj

INNER JOIN msdb.dbo.sysjobsteps JS

ON js.job_id=sj.job_id

INNER JOIN msdb.dbo.sysjobhistory JH

                ON JS.job_id = JH.job_id

                                AND JS.step_id = JH.step_id

LEFT JOIN msdb.dbo.sysjobstepslogs JSL

                ON JS.step_uid = JSL.step_uid

WHERE

                JS.step_id <> 0

                AND JH.job_id = @job_id

                --AND JH.run_status = 0

ORDER BY Jh.run_date desc,Jh.run_time desc

              FOR XML PATH('tr'), TYPE  

    ) AS NVARCHAR(MAX) ) + 

    N'</table>' ;  

 

EXEC msdb.dbo.sp_send_dbmail

                @recipients='mahesh.sambu@gmail.com', 

                @profile_name='DB_Mail Public Profile',

    @subject = 'job history', 

    @body = @tableHTML, 

    @body_format = 'HTML' ; 

OUTPUT:

Please go through this article Configure Mail profile, on how to configure a mail account, profile.

Read More