Search String in Stored Procedure/View/Function

   
    --Search String in Stored Procedures
   
    SELECT [name]
    FROM sys.procedures
    WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%patientid%';
   
    GO
   
    --EXEC Find_InSP 'tblpatient'
     CREATE PROCEDURE dbo.Find_InSP
     @string NVARCHAR(MAX)
     AS
    
     BEGIN
    
     SET NOCOUNT ON;
     SELECT
       cmd = N'EXEC sp_helptext '''
           + QUOTENAME(OBJECT_SCHEMA_NAME([object_id]))
           + '.' + QUOTENAME([name]) + ''';'
     FROM
       sys.procedures
     WHERE
       OBJECT_DEFINITION([object_id]) LIKE N'%' + @string + '%'
     ORDER BY
     cmd;
    
     END
  

 --Search String in Stored Procedure/View/Function
   
    SELECT OBJECT_NAME([object_id])
    FROM sys.sql_modules
    WHERE [definition] LIKE '%patientid%';
   
   
    GO
   
 --EXEC Find_InModule 'tblpatient'
 CREATE PROCEDURE dbo.Find_InModule
 @string NVARCHAR(MAX)
 AS

 BEGIN
 SET NOCOUNT ON;
    SELECT
       cmd = N'EXEC sp_helptext '''
           + QUOTENAME(OBJECT_SCHEMA_NAME([object_id]))
           + '.' + QUOTENAME(OBJECT_NAME([object_id])) + ''';'
    FROM
       sys.sql_modules
    WHERE
       [definition] LIKE N'%' + @string + '%'
    ORDER BY
    cmd;
    END

Query to View/Enable/Disable Database Mail Profile

1.View Profile
   
   select * from msdb.dbo.sysmail_profile


2. Disable Exisiting Profile


 --EXEC msdb.dbo.sysmail_delete_profile_sp
    @profile_name = 'test' ; OR  EXEC msdb.dbo.sysmail_delete_profile_sp @profile_id = [profile_id]
   




3. Enable Profile


sp_CONFIGURE 'show advanced', 1
GO
RECONFIGURE
GO
sp_CONFIGURE 'Database Mail XPs', 1
GO
RECONFIGURE
GO


-- Create new profile

EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'test',
@description = 'Profile to send the  emails'
GO
-- Set the New Profile as the Default
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'test',
@principal_name = 'public',
@is_default = 1 ; -- Make this the default profile
GO


--  created the account


EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'test',
@description = 'Medall SMS email account',
@email_address = 'sms@smtp.medallit.in',


@display_name = 'SMS@medall.in',
@replyto_address = 'IT@medall.in',
@mailserver_name = '10.200.2.28',
@username = 'sms@smtp.medallit.in',
@password = 'Pwd',
@port = 25, -- Check with IT  for correct port
@enable_ssl = FALSE -- Enable secured  communication


-- Added account to the profile:
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'test',
@account_name = 'test',
@sequence_number = 1
GO


--Make sure the define port is allowed in the server.


--check
EXECUTE msdb.dbo.sp_send_dbmail
 @profile_name = 'test',  
@recipients='test@medall.in',
@subject = 'Test e-mail sent from  mail',
@body = 'This is a test message sent from the newly created account'


GO