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.

To Split the String Value

SET NOCOUNT ON;
GO

Declare @data varchar(100) = 'MDEB KLP 657 678989'
Declare @date varchar(100) = '2012-5-06'

DECLARE @T TABLE ([description] varchar(MAX));

INSERT INTO @T ([description]) VALUES (@data)

Insert into [TblEBMeterReading]

SELECT
 @date as Date,
 --Q.x.value('(/e/text())[1]', 'varchar(MAX)') [Category],
 Q.x.value('(/e/text())[2]', 'varchar(25)') CenterCode,
 Q.x.value('(/e/text())[3]', 'int') MeterNo,
 Q.x.value('(/e/text())[4]', 'int') MeterReading
FROM
 @T AS T
 CROSS APPLY
 (SELECT T.[description] AS [text()] FOR XML PATH('')) AS R(s)
 CROSS APPLY
 (SELECT CAST('<e>' + REPLACE(R.s, ' : ', '</e><e>') + '</e>' AS xml)) AS S(x)
 CROSS APPLY
 S.x.nodes('/e') AS N1(e)
 CROSS APPLY
 (SELECT CAST('<e>' + REPLACE(REPLACE(N1.e.value('text()[1]', 'varchar(MAX)'), ' ', '</e><e>'), ' ', '</e><e>') + '</e>' AS xml)) AS Q(x)
GO

StoredProcedure used in Jobs

select sj.name as job_name, st.command
from  sysjobs sj
join sysjobsteps st
on sj.job_id = st.job_id
where st.command like '%storeproce%'

To Split the column values

Table1 
Value   String 
1          Cleo, Smith 

 

Select

 Value  ,

 Substring(FullName, 1,Charindex(',', FullName)-1) as Name, 
 Substring(FullName, Charindex(',', FullName)+1, LEN(FullName)) as  Surname 
from Table1

 

 

Output 
Value  Name   Surname 
1        Cleo     Smith 

Re: Rename all the Table in the database at once

Declare test cursor for select name From sysobjects where name like '%[NEW]' and type='u'
open test
declare @name sysname
declare @newname sysname
declare @str varchar(1000)
declare @newstr varchar(1000)
set @str='sp_rename @name,@newname'
fetch next from test into @name
while @@Fetch_Status=0
begin
print @name
select @newname=@name+'_NEW'   --left(@name, len(@name)-1)
print @newname
select @newstr=replace(@str,'@name',@name)
select @newstr=replace(@newstr,'@newname',@newname)
print @newstr
exec (@newstr)
fetch next from test into @name
end
close test
deallocate test

Merge Command

MERGE INTO [TITEMMASTER] AS TARGET
USING(SELECT DISTINCT ITEM,[Specfication],[PACK SIZE],RATE,[purchase Unit],[Category],[SubCategory],[Our Reference],[Local Allowed] FROM [TITEMMASTER_Centre]) as NEW
on LTRIM(RTRIM(TARGET.itemname))=LTRIM(RTRIM(NEW.item))
AND [SPEC]=NEW.[Specfication]
AND [Pack_Size]=NEW.[Pack Size]
AND [UOM]=NEW.[purchase Unit]
WHEN MATCHED AND
(
--TARGET.[ItemName]<>NEW.[Item]
TARGET.[Amount]<>NEW.[Rate] OR
TARGET.[Category]<>NEW.[Category] OR
TARGET.[Sub_Category]<>NEW.[SubCategory] OR
TARGET.[PurchaseType]<>NEW.[Our Reference]
)
THEN UPDATE SET
TARGET.[ItemName]=NEW.[Item],
TARGET.[SPEC]=NEW.[Specfication],
TARGET.[Pack_Size]=NEW.[Pack Size],
TARGET.[Amount]=NEW.[Rate],
TARGET.[UOM]=NEW.[purchase Unit],
TARGET.[Category]=NEW.[Category],
TARGET.[Sub_Category]=NEW.[SubCategory],
TARGET.[PurchaseType]=NEW.[Our Reference],
TARGET.[UpdatedDate]=GETDATE()

WHEN NOT MATCHED BY TARGET 
THEN INSERT
(
[ItemName],
[SPEC],
[Pack_Size],
[Amount],
[UOM],
[Category],
[Sub_Category],
[PurchaseType],
[ItemType],
[InsertedDate],
[UpdatedDate],
[OBSOLETE]
)
VALUES
(
[Item],
[Specfication],
[Pack Size],
[Rate],
[purchase Unit],
[Category],
[SubCategory],
[Our Reference],
'S',
GETDATE(),
GETDATE(),
'0'
);