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]' + '%'

Searching for table inforation in SQL jobs

I'm putting this here to make it easier to look at job and job step information quickly.


USE [msdb]
GO
/****** Object: StoredProcedure [dbo].[JobInformation_SqlSSISJobs_SelectBySSISFileName]
Script Date: 06/24/2009 13:32:21 ******

This script is used to return every job step where a string occurs

*/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE procedure [dbo].[JobInformation_SqlSSISJobs_SelectBySSISFileName]
@fileName as varchar(125)
AS

/*
declare @fileName as varchar(125)
set @fileName = 'Order Invoice Import Data.dstConfig'
*/

select
[name] as JobName
, step.step_id as JobStepNbr
, step.step_name as JobStepName
, subsystem as JobStepType
, command as JobStepCommand
, database_name AS JobStepDbName
, last_run_date AS JobStepLastRunDate
, last_run_time AS JobStepLastRunTime
, next_run_date AS JobStepNextRunDate
, next_run_time AS JobStepNextRunTime
, run_status AS JobStepLastRunStatus
--, hist.*
FROM msdb.dbo.sysjobs job
INNER JOIN msdb.dbo.sysjobsteps step
ON job.job_id = step.job_id
INNER JOIN msdb.dbo.sysjobschedules sch
ON job.job_id = sch.job_id
LEFT OUTER JOIN dbo.sysjobhistory hist
ON job.job_id = hist.job_id
AND step.step_id = hist.step_id
AND step.last_run_date = hist.run_date
AND step.last_run_time = hist.run_time
WHERE command like '%' + @fileName + '%';



----------------
Now playing: NPR - 0906171: NPR: 06-20-2009 Wait Wait... Don't Tell Me!
via FoxyTunes

SQL [dbo].[usp_send_cdosysmail]

After my last post I realized I should just post [dbo].[usp_send_cdosysmail].

USE [master]
GO
/****** Object: StoredProcedure [dbo].[usp_send_cdosysmail] Script Date: 06/17/2009 11:59:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO



CREATE procedure [dbo].[usp_send_cdosysmail]
@from varchar(500) ,
@to varchar(500) ,
@subject varchar(500),
@body varchar(4000) ,
@smtpserver varchar(25),
@bodytype varchar(10)
as

declare @imsg int
declare @hr int
declare @source varchar(255)
declare @description varchar(500)
declare @output varchar(1000)

exec @hr = sp_oacreate 'cdo.message', @imsg out
exec @hr = sp_oasetproperty @imsg,'configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").value','2'
exec @hr = sp_oasetproperty @imsg, 'configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").value', @smtpserver
exec @hr = sp_oamethod @imsg, 'configuration.fields.update', null
exec @hr = sp_oasetproperty @imsg, 'to', @to
exec @hr = sp_oasetproperty @imsg, 'from', @from
exec @hr = sp_oasetproperty @imsg, 'subject', @subject

-- if you are using html e-mail, use 'htmlbody' instead of 'textbody'.
exec @hr = sp_oasetproperty @imsg, @bodytype, @body
exec @hr = sp_oamethod @imsg, 'send', null

-- sample error handling.
if @hr <>0
select @hr
begin
exec @hr = sp_oageterrorinfo null, @source out, @description out
if @hr = 0
begin
select @output = ' source: ' + @source
print @output
select @output = ' description: ' + @description
print @output
end
else
begin
print ' sp_oageterrorinfo failed.'
return
end
end
exec @hr = sp_oadestroy @imsg

SQL 2000 Sending Email using cdos usp_send_cdosysmail

This wonderful utility provided by Microsoft to not use SQL Mail with SQL Server 2000 to send SMTP email. I usually compile this into master database so it can be used by all databases and agent jobs. This is a sample of how to send and email because I am forever looking for an example.

USE [master]
GO

DECLARE @return_value int

DECLARE @body_text varchar(3000);
SET @body_text = 'Body Header' + char(13)

SET @body_text = @body_text + 'Message line one
'
SET @body_text = @body_text + 'Message line two
'
print @body_text;

EXEC @return_value = [dbo].[usp_send_cdosysmail]
@from = N'noreply.server_name@generac.com',
@to = N'user_name@server_name.com',
@subject = N'Success Email',
@body = @body_text,
@smtpserver = N'mail.server.com',
@bodytype = N'htmlbody'

SELECT 'Return Value' = @return_value

GO

Sql Auditing Changes Stored Procedure Design

/*
I will publish this as I don't have time to finish the documentation but this is the stored procedure that goes with the table below

All users who can drop tables need to be able to send and email or EXECUTE AS need to be applied to allow the stored procedure to both send the email and write the record.
*/

USE [Common]
GO
/****** Object: DdlTrigger [Event_SP_changes] Script Date: 06/02/2009 16:58:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

create trigger [Event_SP_changes]
on database --WITH EXECUTE AS 'UserName'
FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE
AS
DECLARE @FullFile xml
DECLARE @EventType nvarchar(25)
DECLARE @EventTime datetime
DECLARE @LoginName nvarchar(50)
DECLARE @UserName nvarchar(25)
DECLARE @ObjectName nvarchar(50)
DECLARE @ObjectType nvarchar(25)
DECLARE @Command nvarchar(max)
SET @FullFile = eventdata()
SET @EventType = @FullFile.value('(//EVENT_INSTANCE/EventType)[1]', 'nvarchar(25)')
SET @EventTime = @FullFile.value('(//EVENT_INSTANCE/PostTime)[1]', 'DATETIME')
SET @LoginName = @FullFile.value('(//EVENT_INSTANCE/LoginName)[1]', 'nvarchar(50)')
SET @UserName = @FullFile.value('(//EVENT_INSTANCE/UserName)[1]', 'nvarchar(25)')
SET @ObjectName = @FullFile.value('(//EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(50)')
SET @ObjectType = @FullFile.value('(//EVENT_INSTANCE/ObjectType)[1]', 'nvarchar(25)')
SET @Command = @FullFile.value('(//EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)')
--print @command
INSERT INTO [AuditDB].dbo.Common_SP_Events
([EventType]
,[EventTime]
,[LoginName]
,[UserName]
,[ObjectName]
,[ObjectType]
,[Command]
,[FullFile])
SELECT @EventType
, @EventTime
, @LoginName
, @UserName
, @ObjectName
, @ObjectType
, @Command
, @FullFile



GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ENABLE TRIGGER [Event_SP_changes] ON DATABASE



----------------
Now playing: NPR - 0905150: NPR: 05-30-2009 Wait Wait... Don't Tell Me!
via FoxyTunes

Sql Auditing Changes Table Design

/*
This is the start of documenting the process by which I collect information about changes in the database. This is done through ddl triggers and I keep a copy of all of the changes. This step is creating an audit table in the AuditDB. I always give myself one database on every server to collect this kind of information. The later posts will have the stored procedure to populate this table
*/

USE [AuditDB]
GO
/****** Object: Table [dbo].[Common_SP_Events] Script Date: 06/02/2009 16:48:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Common_SP_Events](
[EventType] [nvarchar](25) NULL,
[EventTime] [datetime] NULL CONSTRAINT [DF_Common_SP_Events_EventTime] DEFAULT (getdate()),
[LoginName] [nvarchar](50) NULL,
[UserName] [nvarchar](25) NULL,
[ObjectName] [nvarchar](50) NULL,
[ObjectType] [nvarchar](25) NULL,
[Command] [nvarchar](max) NULL,
[FullFile] [xml] NULL
) ON [PRIMARY];

CREATE CLUSTERED INDEX [cdx_Common_SP_Events_EventTime] ON [dbo].[Common_SP_Events]
(
[EventTime] DESC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF
, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON
, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];