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