sp_utilityFindObjectModifying

USE [IaafHDBDev]
GO
/****** Object:  StoredProcedure [dbo].[sp_utilityFindObjectModifying]    Script Date: 05/21/2008 12:26:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Christian
-- Create date: 21/05/2008
-- Description:	Find object that modify tables
-- =============================================
ALTER PROCEDURE [dbo].[sp_utilityFindObjectModifying] 
	-- Add the parameters for the stored procedure here
	@_ObjectName varchar(100) = null,
	@_ObjectType varchar(2) = null, 
	@_TableRelated varchar(100) = null,
	@_Detailed int = 0
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
    -- Insert statements for procedure here
IF @_Detailed = 0 
	BEGIN
		select sys.objects.name as [OBJECT],sys.objects.type as [TYPE]
		, sys.tables.name AS TABLE_MODIFIED
		from sys.tables
		left join sys.syscomments
		on sys.syscomments.text like'%'+sys.tables.name+'.%'
		inner join sys.objects on
		sys.syscomments.id = sys.objects.object_id
		WHERE sys.tables.name LIKE isnull(@_TableRelated,'%')
		AND sys.objects.type LIKE isnull(@_ObjectType,'%')
		AND sys.objects.name LIKE isnull(@_ObjectName,'%')
		group by sys.objects.name,sys.objects.type, sys.tables.name
		order by sys.objects.name
	END
ELSE IF @_Detailed = 1
	BEGIN
		select sys.objects.name as [OBJECT],sys.objects.type as [TYPE],
		case
			when sys.syscomments.text like '%INSERT%.'+sys.columns.name+'%' then 'I'
			when sys.syscomments.text like '%DELETE%.'+sys.columns.name+'%' then 'D'
			when sys.syscomments.text like '%UPDATE%.'+sys.columns.name+'%' then 'U'
			when sys.syscomments.text like '%SELECT%.'+sys.columns.name+'%' then 'S'
			when sys.syscomments.text like '%GROUP%.'+sys.columns.name+'%' then 'GR'	
		end AS [ACTION]
		, sys.tables.name AS TABLE_MODIFIED, sys.columns.name as [COLUMNS]
		from sys.tables
		left join sys.columns
		on sys.columns.object_id = sys.tables.object_id
		left join sys.syscomments
		on sys.syscomments.text like'%'+sys.tables.name+'.%'
		and sys.syscomments.text like '%.'+sys.columns.name+'%'
		inner join sys.objects on
		sys.syscomments.id = sys.objects.object_id
		WHERE sys.tables.name LIKE isnull(@_TableRelated,'%')
		AND sys.objects.type LIKE isnull(@_ObjectType,'%')
		AND sys.objects.name LIKE isnull(@_ObjectName,'%')
		group by sys.syscomments.text,sys.objects.name,sys.objects.type, sys.tables.name,sys.columns.name
		order by sys.objects.name
	END 
END