Stored Procedure Text

A quick script to search the text of current stored procedures in the current database.

SELECT
[name] as SpName
, obj.id as SpId
, com.[text] as SpText
, crDate as SpCreateDate
, *
FROM sys.sysobjects obj
LEFT OUTER JOIN sys.syscomments com
ON obj.id = com.id
WHERE type IN ('P', 'TF') -- P = User Stored Proc TF = User Defined Function
AND com.[text] LIKE '%' + '[TABLE NAME]' + '%'

SQL 2005 Database Mail

I put this here so I have a standard template for sending mail. This script assumes there is a public email account with the name [Instance Name]Admin to send the email. The coalesce statement first checks if there is an instance name. The function will return NULL is it is the default. The second value will then insert the machine name to generate the user name. This is included to allow the same email script to be used on multiple servers especially test and production with no changes to the script.

There are many more options i

DECLARE @profile varchar(100)
DECLARE @EmailTo varchar(128)
DECLARE @Subjectvarchar(128)
DECLARE @Body varchar(max)
DECLARE @BodyType varchar(25)

SELECT @profile = CAST(COALESCE(SERVERPROPERTY ( 'InstanceName' ), SERVERPROPERTY ( 'ServerName')) AS varchar(80)) + 'Admin'
SET @EmailTo = 'address@domain.com'
SET @Subject = 'test'
SET @Body = 'test'
SET @BodyType = 'HTML' --TEXT

EXEC msdb.dbo.sp_send_dbmail
@profile_name = @profile,
@recipients = @EmailTo ,
@subject = @Subject,
@body = @Body ,
@body_format = @BodyType


----------------
Now playing: NPR - 0905136: NPR: 05-16-2009 Wait Wait... Don't Tell Me!