Search content in which SP

create PROCEDURE [dbo].[autosearch]
(
@text VARCHAR(1024)
)   
AS   
BEGIN   

 SELECT   
  TABLE_NAME   
 FROM   
  INFORMATION_SCHEMA.TABLES T   
 WHERE   
  charindex(@text, T.TABLE_NAME)>0   
     
 -- columns   
 SELECT    
  C.TABLE_NAME, C.COLUMN_NAME   
 FROM   
  INFORMATION_SCHEMA.COLUMNS C   
 WHERE   
  charindex(@text, C.COLUMN_NAME)>0   
   
 -- views   
 SELECT    
  V.TABLE_NAME AS VIEW_NAME   
 FROM    
  information_schema.VIEWS V   
 WHERE    
  charindex(@text, V.VIEW_DEFINITION)>0   
     
 -- stored procs   
 SELECT    
  R.ROUTINE_NAME    
 FROM    
  information_schema.routines r    
 WHERE    
  charindex(@text, r.ROUTINE_DEFINITION)>0   
END   
   

Excel attachment with HTML in SQL Server

DECLARE @tableHTML NVARCHAR(MAX)
DECLARE @VSubject varchar(50)
Declare @sql1 Varchar(4000)                                
Declare @FileName Varchar(500)
declare @tab varchar(10)=char(9)
Declare @date varchar(30)=getdate()-2
declare @nextline varchar(10) , @body1 varchar(2000),@SubTitle varchar(200)
 
Truncate table Tsurvey

set @VSubject = 'Survey Updates On ' + convert(varchar(11),@date ,106)
SET @FileName='\\10.200.2.40\MartReports\Survey_'+cast(convert(date,@date) as varchar)+'.xls'
set @nextline= ''+char(13) 
set @SubTitle= 'Survey Details'

SET @tableHTML = N'<H2>'+@SubTitle+'</H2>'  
+N'<table border="3">' 
+N'<th><p style="color:blue;margin-left:20px;">ID</p></th>'   
+N'<th><p style="color:Red;margin-left:20px;">CreatedDate</p></th>'   
+N'<th><p style="color:Green;margin-left:20px;">PhoneNumber</p></th>'                                    
+N'<th><p style="color:Blue;margin-left:20px;">EmpCode</p></th>'                                   
+N'<th><p style="color:Orange;margin-left:20px;">PatientName</p></th>'      
+N'<th><p style="color:blue;margin-left:20px;">Question</p></th>' 
+N'<th><p style="color:Green;margin-left:20px;">Answer</p></th>' 
+                           
CAST                                   
( ( select 
td = a.ID,'', 
td = a.CreatedDate,'', 
td = a.PhoneNumber,'', 
td = a.EmpCode,'', 
td = a.PatientName,'', 
td = a.Question,'', 
td = a.Answer,''
 
from  
(

select ID,convert(varchar(11),CreatedDate,106) as CreatedDate,PhoneNumber,EmpCode,PatientName,Question,Answer
from [tbl_UserSurveyQuestionAnswer] where cast([CreatedDate] as date) = cast(@date as date)

) as A 
 
FOR XML PATH('tr'), TYPE )                                   
AS NVARCHAR(MAX) ) + N'</table>' ;      
print @tableHTML

Set @body1 = @tableHTML +@nextline+ 'Please find the attachment Excel.'

----Excel----
insert Tsurvey (ID,CreatedDate,PhoneNumber,EmpCode,PatientName,Question,Answer)
select ID,convert(varchar(11),CreatedDate,106) as CreatedDate,PhoneNumber,EmpCode,PatientName,Question,Answer
from [tbl_UserSurveyQuestionAnswer] where cast([CreatedDate] as date) = cast(@date as date)

select @sql1 = 'bcp "select ''ID'',''CreatedDate'',''PhoneNumber'',''EmpCode'',''PatientName'',''Question'',''Answer'' UNION ALL SELECT cast(ID  as varchar(50)),cast([CreatedDate]  as varchar(50)),cast([PhoneNumber]  as varchar(50)),[EmpCode],[PatientName],[Question],[Answer] FROM survey.dbo.tsurvey  " QUERYOUT '+@FileName+' -c -T -S '+ @@servername  
   
--print @sql1

if  LEN(@tableHTML)>0
begin

EXEC MASTER..xp_cmdshell @sql1
EXEC msdb.dbo.sp_send_dbmail    
       
      --@profile_name = 'hrms',     
      @recipients = 'mailid' ,  
      --@copy_recipients = @CC  ,
      @subject = @VSubject ,  
      @body = @body1 , 
      @file_attachments=@FileName ,    
      @body_format = 'HTML' ;  

end

UPDATE table using Join statement

update a set sex=b.sex , age=b.age , dob=b.Dob , cellphone=b.CellPhone , emailid=EmailId1
--select *
from [MedallMart].[dbo].[TblTransactionData] a
inner join 
(select 
centerid,
patientID,
FirstName,
case when gender = 0 then 'Male' when gender = 1 then 'Female' else 'Others' end as Sex,
age ,
Dob,
CellPhone,
EmailId1
from tblpatient) as b on a.CenterID=b.CenterID and a.InPatientId=b.patientID and a.PatientName=b.FirstName

To Delete the duplicate rows in the table by Ranking

   DELETE A  FROM (  
  SELECT ROW_NUMBER()OVER(PARTITION BY CenterRefID,CorporateRefID,CenterID,obsolute ORDER BY CenterRefID,CorporateRefID,CenterID DESC)   RANK1,CenterRefID,CorporateRefID,CenterID,obsolute
  FROM TblCorpRefIDMapping  ) A
   WHERE RANK1 > 1

To create Trigger for the Table Column

Create
TRIGGER [dbo].[Trg_TblVisits]
ON [dbo].[TblVisits]
AFTER INSERT,UPDATE
AS
BEGIN
SET NOCOUNT ON;
UPDATE
A SET LastModifiedDate = GETDATE()
FROM
TblVisits A INNER JOIN inserted B
ON
A.VISITID = B.VISITID

END

To bring the row data in one column

SELECT top 1  
       STUFF((SELECT ',' + b.Coldisp from tempd b Where a.CenterName=B.CenterName FOR XML PATH(''), TYPE ).value('.','varchar(max)') ,1,1, '') AS StudyName  
FROM tempd a  
    GROUP BY  CenterName,Coldata,Coldisp

How to resolve Error 22051?

Jobs failed with below error message.

Error Message:

Executed as user: XXXXXX-svc. Mail queued. [SQLSTATE 01000] (Message 0)  File attachment or query results size exceeds allowable value of 1000000 bytes. [SQLSTATE 42000] (Error 22051).  The step failed.

Resolution Steps:

Go to Database Mail under Management in Sql Server Management Studio -->Select Configuration Task as View or Change system parameters -->Give Maximum File Size (Bytes) as to 1.5 million

That's it

Now mail sending commands in the job will execute successfully.