Learning Code & Activity

Find a column in SQL database tables

SELECT s.NAME AS ColumnName
 ,sh.NAME + '.' + o.NAME AS ObjectName
 ,o.type_desc AS ObjectType
 ,CASE 
  WHEN t.NAME IN (
    'char'
    ,'varchar'
    )
   THEN t.NAME + '(' + CASE 
     WHEN s.max_length < 0
      THEN 'MAX'
     ELSE CONVERT(VARCHAR(10), s.max_length)
     END + ')'
  WHEN t.NAME IN (
    'nvarchar'
    ,'nchar'
    )
   THEN t.NAME + '(' + CASE 
     WHEN s.max_length < 0
      THEN 'MAX'
     ELSE CONVERT(VARCHAR(10), s.max_length / 2)
     END + ')'
  WHEN t.NAME IN ('numeric')
   THEN t.NAME + '(' + CONVERT(VARCHAR(10), s.precision) + ',' + CONVERT(VARCHAR(10), s.scale) + ')'
  ELSE t.NAME
  END AS DataType
 ,CASE 
  WHEN s.is_nullable = 1
   THEN 'NULL'
  ELSE 'NOT NULL'
  END AS Nullable
 ,CASE 
  WHEN ic.column_id IS NULL
   THEN ''
  ELSE ' identity(' + ISNULL(CONVERT(VARCHAR(10), ic.seed_value), '') + ',' + ISNULL(CONVERT(VARCHAR(10), ic.increment_value), '') + ')=' + ISNULL(CONVERT(VARCHAR(10), ic.last_value), 'null')
  END + CASE 
  WHEN sc.column_id IS NULL
   THEN ''
  ELSE ' computed(' + ISNULL(sc.DEFINITION, '') + ')'
  END + CASE 
  WHEN cc.object_id IS NULL
   THEN ''
  ELSE ' check(' + ISNULL(cc.DEFINITION, '') + ')'
  END AS MiscInfo
 ,CASE 
  WHEN t.NAME IN (
    'char'
    ,'varchar'
    )
   THEN CASE 
     WHEN s.max_length < 0
      THEN 0
     ELSE CONVERT(VARCHAR(10), s.max_length)
     END
  WHEN t.NAME IN (
    'nvarchar'
    ,'nchar'
    )
   THEN CASE 
     WHEN s.max_length < 0
      THEN 0
     ELSE CONVERT(VARCHAR(10), s.max_length / 2)
     END
  END val
INTO #temp
FROM sys.columns s
INNER JOIN sys.types t ON s.system_type_id = t.user_type_id
 AND t.is_user_defined = 0
INNER JOIN sys.objects o ON s.object_id = o.object_id
INNER JOIN sys.schemas sh ON o.schema_id = sh.schema_id
LEFT JOIN sys.identity_columns ic ON s.object_id = ic.object_id
 AND s.column_id = ic.column_id
LEFT JOIN sys.computed_columns sc ON s.object_id = sc.object_id
 AND s.column_id = sc.column_id
LEFT JOIN sys.check_constraints cc ON s.object_id = cc.parent_object_id
 AND s.column_id = cc.parent_column_id
WHERE --t.name in ('nvarchar','nchar','char','varchar')
 S.NAME LIKE 'attendance%' --<--Write your columnname here
ORDER BY sh.NAME + '.' + o.NAME
 ,s.column_id

SELECT *
FROM #temp --where val>500

DROP TABLE #temp

DBA Scenario 1: How to handle 100% CPU Utilization

Hi guys! Today we shall discuss the hot topic how to handle 100% CPU Utilization. DBA face this kind of situation often in their daily life. Some time it is must to solve this situation in production environment as this will hamper the business activity in terms of transactions and money.

The methods we are about to discuss, help only if SQL server is the culprit, You need to first identify whether SQL is consuming all memory resources, then only you need to apply these methods otherwise it will not help you-

I shall cover two methods; both methods are not same and can’t be used at same time. Their aim is same to lower the memory usage but they are applied over different scenario.

Before begin, I want to discuss a myth that is, mostly it is assumed that longer running queries are problem, but it is not true all the time, yes it may cause problem but small concurrent running queries having maximum worker time or maximum execution count can also be a problem. For example a query which is executing before 1 sec can’t be a problem. But the same query if executed 1 lac time concurrently (at the same time) can cause issue.

As per my experience, mostly select queries are the culprit and create such situation so that sql server starts consuming 100 % of memory resources. You can use task manager or resource monitor to find the CPU usage.

Method 1 :

It is a traditional approach, mostly used by DBAs. Whenever this kind of situation arises, you need to first check for the intense processes running on the server. For this you need to continuously execute one procedure sp_who2 and monitor which spid is increasing gradually, then you need to identify what is going on that session for that use dbcc inputbuffer(<spid>), if it is select query you can kill it but you should not kill transaction and queries having insert update delete on sql tables.

sp_who2

dbcc inputbuffer(<spid>)

kill <spid>

Note: You need to look for spid greater than 50 because less than 50 spids are used by Sql server for its internal working.

Method 2 :

It is more granular approach to optimize the query. In this approach you need to use few DMV’s. Sql server increases the execution_count for same query, if the definition of the query changes new plan is created. So you need to find out the queries having maximum exeution_count and maximum total_worker_time. When you find the record you will get the query plan, which you need to copy and paste to another dmv that is sys.dm_exec_query_stats.

Select * from sys.dm_exec_query_stats order by execution_count desc

Select * from sys.dm_exec_query_stats order by total_worker_time desc

Select * from sys.dm_exec_query_plan(<plan_handle>)

So from above query we will get the execution plan and from there we can view xml view of query and find parameters for the query. After getting queries you can apply sargable rules, these rules are used to optimize queries.

This is how you can trace costly queries and handle 100% CPU Utilization

SSRS - Download all Report files Or Single (RDL) from Report Server

Hi friends! Today my manager asked me to download all reports from Report Server and check in to TFS.

There were plenty of reports almost 1000, so it is difficult to download one by one. I thought this kind of problem come to most of the DBAs so decided to write this blog, I found same script on google and modified it-

Prerequisites & Notes :

1. xp_cmdshell should be enabled. (Part 1)

2. SQL Engine Account should proper write permission on Output Folder where you will get all your reports

3. Please read comments before execution and follow (Part 2)

4. Change the Report Server database on the query

Part 1 : Enable xp_cmdshell

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'show advanced options', 0
GO
RECONFIGURE
GO

Part 2 : Script Implementation

If you want all reports in output folder whether they were published in different folders
in SSRS Report Server then put Null else define folder name
eg: DECLARE @ReportFolderPath AS VARCHAR(500) ='/Folder1/Folder2/'

DECLARE @ReportFolderPath AS VARCHAR(500) = null 
/*
If you want some specific report then put its name else put Null
eg: DECLARE @ReportName AS VARCHAR(500) = 'Bill'
*/
DECLARE @ReportName AS VARCHAR(500) = null
/* 
This is the output folder where reports will be generated. Make sure Sql Server Engine Account 
has proper permission on this
*/
DECLARE @OutputFolderLocation AS VARCHAR(500) = 'C:\ReportFolder\'
--To check Folder path
SET @OutputFolderLocation = REPLACE(@OutputFolderLocation,'\','/')
/*
This for bcp access to sql server. If you want to access through sql login use
eg : DECLARE @BCPComponents VARCHAR(1000)='-S"MachineName\SQL2012" -U"SA" -P"Pa$$word"'
or If you want from windows account having permission on sql then
eg : DECLARE @BCPComponents VARCHAR(1000)='-S"1MachineName\SQL2012"'
else null
*/
DECLARE @BCPComponents VARCHAR(1000)= null
DECLARE @SqlQuery AS NVARCHAR(MAX)

IF LTRIM(RTRIM(ISNULL(@OutputFolderLocation,''))) = ''
BEGIN
  SELECT 'Access denied!!!'
END
ELSE
BEGIN


 SET @SqlQuery = STUFF((SELECT
                      ';EXEC master..xp_cmdshell ''bcp  " ' +
                      ' SELECT ' +
                      ' CONVERT(VARCHAR(MAX), ' +
                      '       CASE ' +
                      '         WHEN LEFT(C.Content,3) = 0xEFBBBF THEN STUFF(C.Content,1,3,'''''''') '+
                      '         ELSE C.Content '+
                      '       END) ' +
                      ' FROM ' +
                      ' [ReportServer$SQL2012].[dbo].[Catalog] CL ' +
                      ' CROSS APPLY (SELECT CONVERT(VARBINARY(MAX),CL.Content) Content) C ' +
                      ' WHERE ' +
                      ' CL.ItemID = ''''' + CONVERT(VARCHAR(MAX), CL.ItemID) + ''''' " queryout "' + @OutputFolderLocation + '' + CL.Name + '.rdl" ' + '-T -c -x '+COALESCE(@BCPComponents, '')+''''
                    FROM
                      [ReportServer$SQL2012].[dbo].[Catalog] CL
                    WHERE
                      CL.[Type] = 2 --Report
                      AND '/' + CL.[Path] + '/' LIKE COALESCE('%/%' + @ReportFolderPath + '%/%', '/' + CL.[Path] + '/')
                      AND CL.Name LIKE COALESCE('%' + @ReportName + '%', CL.Name)
                    FOR XML PATH('')), 1,1,'')
  
  SELECT @SqlQuery
  
  --Execute the Dynamic Query
  EXEC SP_EXECUTESQL @SqlQuery
END

Reference : VinayPugalia

Monitor Database Size Location through query and mailer

Hi friends! One of my friend asked me to help him creating a mailer that gives information about all databases of a server. I tried and created this-

----------------------------------------------------------------------

Use master
Go

Create Table #temp
(
    DatabaseName sysname,
    LogicalName sysname,
    physical_name varchar(500),
    size decimal (18,2),
    FreeSpace decimal (18,2),
 [DBState] varchar(500)
)   
Exec sp_msforeachdb '
Use [?];
Insert Into #temp (DatabaseName, LogicalName, physical_name, Size, FreeSpace,[DBState])
    Select DB_NAME() AS [DatabaseName], Name as LogicalName,  physical_name,
    Cast(Cast(Round(cast(size as decimal) * 8.0/1024.0,2) as decimal(18,2)) as nvarchar) Size,
    Cast(Cast(Round(cast(size as decimal) * 8.0/1024.0,2) as decimal(18,2)) -
    Cast(FILEPROPERTY(name, ''SpaceUsed'') * 8.0/1024.0 as decimal(18,2)) as nvarchar) As FreeSpace,
 state_desc as [DBState]
    From sys.database_files
'
Select ROW_NUMBER() Over(Order by DatabaseName) RowNo, * From #temp


---------mailer
Declare @MailSubject varchar(1000)='Database Monitor Info for Servers '+Isnull(Cast(@@ServerName as varchar(500)),'')
Declare @MailBodyHeader varchar(3000)='Hi,<br><br>Please find the details of databases-<br><br>
  <TABLE BORDER="0" CELLPADDING="0" CELLSPACING="0" WIDTH="100%" style="border-top:1px solid black; border-left:1px solid black;"><TBODY>
          <TR><TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="#2f75b5" ALIGN="CENTER" colspan="7"><FONT FACE="Tahoma" SIZE="3" WIDTH="100PX" Color="white"><B>Database Monitor Info</B></FONT></TD></TR>
          <TR><TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="#2f75b5" ALIGN="CENTER" colspan="7"><FONT FACE="Tahoma" SIZE="2" WIDTH="100PX" Color="white"><B>Status </B></FONT></TD></TR>
          <TR><TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="#FCE4D6" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2"><B>RowNo</B></FONT></TD>
           <TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="#FCE4D6" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2"><B>DatabaseName</B></FONT></TD>
           <TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="#FCE4D6" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2"><B>LogicalName</B></FONT></TD>
           <TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="#FCE4D6" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2"><B>physical_name</B></FONT></TD>
           <TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="#FCE4D6" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2"><B>size</B></FONT></TD>
           <TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="#FCE4D6" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2"><B>FreeSpace</B></FONT></TD>
           <TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="#FCE4D6" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2"><B>DBState</B></FONT></TD>
           </TR>'
Declare @MailBody varchar(max)
Select @MailBody= Isnull(@MailBody,'')+'<TR><TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="white" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2"><B>'+Isnull(Cast(ROW_NUMBER() Over(Order by DatabaseName) as varchar(10)),'')+'</B></FONT></TD>
           <TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="white" ALIGN="Left"><FONT FACE="Tahoma" SIZE="2"><B>'+Isnull(Cast(DatabaseName as varchar(500)),'')+'</B></FONT></TD>
           <TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="white" ALIGN="Left"><FONT FACE="Tahoma" SIZE="2">'+Isnull(Cast(LogicalName as varchar(500)),'')+'</FONT></TD>
           <TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="white" ALIGN="Left"><FONT FACE="Tahoma" SIZE="2">'+Isnull(Cast(physical_name as varchar(2000)),'')+'</FONT></TD>
           <TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="white" ALIGN="Center"><FONT FACE="Tahoma" SIZE="2">'+Isnull(Cast(size as varchar(50)),'')+'</FONT></TD>
           <TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="white" ALIGN="Center"><FONT FACE="Tahoma" SIZE="2">'+Isnull(Cast(FreeSpace as varchar(50)),'')+'</FONT></TD>
           <TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="white" ALIGN="Left"><FONT FACE="Tahoma" SIZE="2">'+Isnull(Cast(DBState as varchar(50)),'') +'</FONT></TD>
           </TR>'
     From #temp

Set  @MAILBODY= @MailBodyHeader +@MAILBODY+'</Table>'
IF((Select Count(1) from #temp)>0)
Begin
EXEC MSDB.DBO.SP_SEND_DBMAIL 
     @RECIPIENTS ='vimal.lohani@gmail.com',
     --@COPY_RECIPIENTS = 'vimal.lohani@gmail.com',                                   
     @BODY = @MailBody,
     @SUBJECT = @MailSubject,
     @BODY_FORMAT = 'HTML',
     @PROFILE_NAME ='dbamail' --Your profile name
End
---------mailer enddrop table #temp

Monitor Windows Server having SQL Services Agent Services Reporting Services

Hi guys! Yesterday my boss asked me to build a tool to monitor Windows servers and Sql server services. I searched on Google and find few codes, i modified the code changed powershell concept and made below code.

In this post I shall tell you about how you monitor your all servers. The only thing you need is SQL Server which runs with an account having admin privilege on the network, or you can monitor those systems, the account having admin rights.

Lets start-

Create a database and two tables-

----------------------------------------------------------------------

Use master
Go
Create Database DBAMonitor
GO
Use DBAMonitor
GO 

CREATE TABLE [dbo].[tbl_ServerInfo](
 [Id] [int] identity(1,1) Not NULL,
 [WindowsServerName] [varchar](100) Not NULL,
 [SQLinstanceName] [varchar](100)  NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[tbl_ServiceInfo](
 [SQLServiceName] [varchar](500) NULL,
 [servicestatus] [varchar](100) NULL,
 [windowservername] [varchar](500) NULL,
 [startmode] [varchar](100) NULL,
 [UpdateDate] [datetime] NULL DEFAULT GETDATE(),
 [startname] [varchar](1000) NULL,
 [InstanceName] [varchar](1000) NULL,
 [ServerID] INT NULL
) ON [PRIMARY]

----------------------------------------------------------------------

Please add your server details on tbl_serverInfo you want to monitor-

----------------------------------------------------------------------

--For example

insert into [tbl_ServerInfo](windowsServerName,SQLinstanceName) values ('Proddb','Dashboard')
insert into [tbl_ServerInfo](windowsServerName,SQLinstanceName) values ('192.168.0.512','sql2008r2')
insert into [tbl_ServerInfo](windowsServerName,SQLinstanceName) values ('192.168.0.500','')
insert into [tbl_ServerInfo](windowsServerName,SQLinstanceName) values ('192.168.0.510','')

--Checking details

----------------------------------------------------------------------

Now we need to run below script, but make sure xp_cmdshell is enabled. To enable it, please run below code-

----------------------------------------------------------------------

sp_configure 'Show Advanced Options',1
go
reconfigure
go
sp_configure 'xp_cmdshell',1
go   
reconfigure
go

----------------------------------------------------------------------

Now run the script and look the power-

----------------------------------------------------------------------

set NoCount on
--Truncate table DBAMonitor..[tbl_ServiceInfo]
-- Variable to store windows server name
DECLARE @server_name varchar(100)
DECLARE @SQLInstance_name varchar(100)
DECLARE @SrvrID int
-- table to store PowerShell script output
CREATE TABLE #ServiceInfoData
(line varchar(max) null)
-- Declaring cursor to fetch windows server name
DECLARE server_cursor CURSOR FOR 
select distinct LTRIM(rtrim(windowsservername)) as windowsServerName,SQLInstanceName,Id from DBAMonitor..[tbl_ServerInfo]
OPEN server_cursor
FETCH NEXT FROM server_cursor 
INTO @server_name, @SqlInstance_Name,@SrvrID
WHILE @@FETCH_STATUS = 0
BEGIN
declare @svrName varchar(255)
declare @sql varchar(800)
set @svrName = @server_name
-- Preparing PowerShell Dynamic Statement
set @sql = 'powershell.exe -c "get-wmiobject -Class win32_service -Computername ' + QUOTENAME(Isnull(@svrName,''),'''') + ' | where {$_.name -like ''MSSQLServer'' -OR $_.name -like ''MSSQL$' + Isnull(@SqlInstance_Name,'') + ''' -OR $_.name -like ''SQLSERVERAGENT'' -OR $_.name -like ''ReportServer$' + Isnull(@SqlInstance_Name,'') + ''' -OR $_.name -like ''SQLAgent$' + Isnull(@SqlInstance_Name,'') + ''' } | select-object  Name,state,systemname,startmode,startname | %{$_.name+''|''+$_.state+''%''+$_.systemname+''*''+$_.startmode+''@''+$_.startname+''!''} "'
-- Inserting PowerShell Output to temporary table
--print @sql
insert #ServiceInfoData 
EXEC xp_cmdshell @sql
-- Deleting the rows which contains error or has not sufficient data
delete from #ServiceInfoData  where len(line) < 30
update #ServiceInfoData set line = line + '!' where line not like '%!%'
IF (SELECT COUNT(*) FROM #ServiceInfoData where line like '%Get-Wmi%') = 0
begin
insert into DBAMonitor..[tbl_ServiceInfo](SQLServiceName,servicestatus,windowservername,startmode,startname,InstanceName,ServerId)
select  rtrim(ltrim(SUBSTRING(line,1,CHARINDEX('|',line) -1))) as SQLServiceName 
,(rtrim(ltrim(SUBSTRING(line,CHARINDEX('|',line)+1, (CHARINDEX('%',line) -1)-CHARINDEX('|',line)) ))) as ServiceStatus
--,(rtrim(ltrim(SUBSTRING(line,CHARINDEX('%',line)+1, (CHARINDEX('*',line) -1)-CHARINDEX('%',line)) ))) as WindowsServerName 
,@server_name
,(rtrim(ltrim(SUBSTRING(line,CHARINDEX('*',line)+1, (CHARINDEX('@',line) -1)-CHARINDEX('*',line)) ))) as startmode
,(rtrim(ltrim(SUBSTRING(line,CHARINDEX('@',line)+1, (CHARINDEX('!',line) -1)-CHARINDEX('@',line)) ))) as startname
,@SQLInstance_name
,@SrvrID
from #ServiceInfoData
where line is not null and LEN(line) > 30
end
-- Clearing output table
truncate table #ServiceInfoData
-- Next windows record
FETCH NEXT FROM server_cursor 
INTO @server_name,@SQLInstance_name,@SrvrID
END 
CLOSE server_cursor;
DEALLOCATE server_cursor;
-- dropping the temporary table
drop table #ServiceInfoData
Select 
Srvr.windowsServerName
,Srvr.SQLinstanceName 
,sevi.SQLServiceName
,sevi.startname [ServiceAccount]
,sevi.servicestatus
,sevi.UpdateDate [StatusTime]
,Case when sevi.serverid is null Then 'Server is down or Access denied!!' Else 'Check status!!' End [Remarks] 
From DBAMonitor..[tbl_ServerInfo] srvr
Left Join DBAMonitor..[tbl_ServiceInfo] sevi on srvr.ID=sevi.Serverid

---------------------------------------------------------

For better results, Create a job and run the above script from sql agent.

You can also create mailer for same to notify.

The below code is to notify if any service is stopped or any server is down.

----------------------------------------------------------------------

Declare @MailSubject varchar(1000)='Monitor info for Windows Servers and Sql Services'
Declare @MailBodyHeader varchar(3000)='Hi,<br><br>Please find the details of servers or services, which are not working perfectly-<br><br>
  <TABLE BORDER="0" CELLPADDING="0" CELLSPACING="0" WIDTH="100%" style="border-top:1px solid black; border-left:1px solid black;"><TBODY>
          <TR><TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="#2f75b5" ALIGN="CENTER" colspan="7"><FONT FACE="Tahoma" SIZE="3" WIDTH="100PX" Color="white"><B>Server Monitor Info</B></FONT></TD></TR>
          <TR><TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="#2f75b5" ALIGN="CENTER" colspan="7"><FONT FACE="Tahoma" SIZE="2" WIDTH="100PX" Color="white"><B>Status </B></FONT></TD></TR>
          <TR><TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="#FCE4D6" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2"><B>Windows Server Name</B></FONT></TD>
           <TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="#FCE4D6" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2"><B>Sql Instance Name</B></FONT></TD>
           <TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="#FCE4D6" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2"><B>Sql Service Name</B></FONT></TD>
           <TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="#FCE4D6" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2"><B>Service Account</B></FONT></TD>
           <TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="#FCE4D6" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2"><B>Service Status</B></FONT></TD>
           <TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="#FCE4D6" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2"><B>Status Time</B></FONT></TD>
           <TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="#FCE4D6" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2"><B>Remarks</B></FONT></TD>
           </TR>'
Declare @MailBody varchar(max)
Select @MailBody= Isnull(@MailBody,'')+'<TR><TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="white" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2"><B>'+Isnull(Srvr.windowsServerName,'')+'</B></FONT></TD>
           <TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="white" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2"><B>'+Isnull(Srvr.SQLinstanceName,'')+'</B></FONT></TD>
           <TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="white" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2">'+Isnull(sevi.SQLServiceName,'')+'</FONT></TD>
           <TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="white" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2">'+Isnull(sevi.startname,'')+'</FONT></TD>
           <TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="white" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2">'+Isnull(sevi.servicestatus,'')+'</FONT></TD>
           <TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="white" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2">'+Isnull(Cast(sevi.UpdateDate as varchar(30)),'')+'</FONT></TD>
           <TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="white" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2">'+Case when sevi.serverid is null Then 'Server is down or Access denied!!' Else 'Check status!!' End +'</FONT></TD>
           </TR>'
     From DBAMonitor..[tbl_ServerInfo] srvr
     Left Join DBAMonitor..[tbl_ServiceInfo] sevi on srvr.ID=sevi.Serverid
     Where servicestatus<>'Running' or servicestatus is null

Set  @MAILBODY= @MailBodyHeader +@MAILBODY+'</Table>'
IF((Select Count(1) from DBAMonitor..[tbl_ServerInfo] srvr Left Join DBAMonitor..[tbl_ServiceInfo] sevi on srvr.ID=sevi.Serverid Where servicestatus<>'Running' or servicestatus is null)>0)
Begin
EXEC MSDB.DBO.SP_SEND_DBMAIL 
     @RECIPIENTS ='vimal.lohani@gmail.com',
     @COPY_RECIPIENTS = 'vimal.lohani@gmail.com',                                   
     @BODY = @MailBody,
     @SUBJECT = @MailSubject,
     @BODY_FORMAT = 'HTML',
     @PROFILE_NAME ='dbamail' --Your profile name
End

----------------------------------------------------------------------

You can also schedule above script through agent.

Reference

MSSqlTips

Find a value in all columns of all tables

Declare @SearchString varchar(50) ='kishor.b'--<--Define your search string here

Declare  @ResultsFound TABLE(ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET  @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchString + '%','''')

WHILE @TableName IS NOT NULL
BEGIN
    SET @ColumnName = ''
    SET @TableName = 
    (
        SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
        FROM    INFORMATION_SCHEMA.TABLES
        WHERE       TABLE_TYPE = 'BASE TABLE'
            AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
            AND OBJECTPROPERTY(
                    OBJECT_ID(
                        QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                         ), 'IsMSShipped'
                           ) = 0
    )

    WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
    BEGIN
        SET @ColumnName =
        (
            SELECT MIN(QUOTENAME(COLUMN_NAME))
            FROM    INFORMATION_SCHEMA.COLUMNS
            WHERE       TABLE_SCHEMA    = PARSENAME(@TableName, 2)
                AND TABLE_NAME  = PARSENAME(@TableName, 1)
                AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
                AND QUOTENAME(COLUMN_NAME) > @ColumnName
        )

        IF @ColumnName IS NOT NULL
        BEGIN
            INSERT INTO @ResultsFound
            EXEC
            (
                'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
                FROM ' + @TableName + ' (NOLOCK) ' +
                ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
            )
        END
    END 
END

SELECT ColumnName, ColumnValue FROM @ResultsFound