PROCEDURE FOR FIND TRIGGERS AND VIEWS ON PARTICULAR TABLE

PROCEDURE FOR FINDING TRIGGERS AND VIEWS ON PARTICULAR TABLE:

USE SAMPLE_DATABASE
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- EXEC USP_VIEW_TRIGGERS_LIST @TNAME = 'EMP',@TYPE = NULL

CREATE PROC [dbo].[USP_VIEW_TRIGGERS_LIST](@TNAME VARCHAR(100),@TYPE VARCHAR(100) = NULL)
AS
BEGIN
CREATE TABLE #TEMP(TNAME VARCHAR(100),[VIEWS/TRIGGERS] VARCHAR(MAX))
DECLARE @STR VARCHAR(MAX)
SELECT @STR=''

IF(@TYPE IS NULL)
BEGIN

SELECT @STR=@STR+', '+VIEW_NAME FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE WHERE TABLE_NAME=@TNAME
INSERT INTO #TEMP
SELECT @TNAME,STUFF(@STR,1,2,'')
SELECT @STR=''

SELECT @STR=@STR+', '+T.[NAME] FROM SYS.TRIGGERS T JOIN SYS.OBJECTS O ON T.[PARENT_ID] = O.[OBJECT_ID] WHERE O.[NAME] =@TNAME
INSERT INTO #TEMP
SELECT @TNAME,STUFF(@STR,1,2,'')


END
ELSE
IF(@TYPE ='VIEW')
BEGIN

SELECT @STR=@STR+', '+VIEW_NAME FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE WHERE TABLE_NAME=@TNAME
INSERT INTO #TEMP
SELECT @TNAME,STUFF(@STR,1,2,'')
SELECT @STR=''

END
ELSE
IF(@TYPE = 'TRIGGER')
BEGIN


SELECT @STR=@STR+', '+T.[NAME] FROM SYS.TRIGGERS T JOIN SYS.OBJECTS O ON T.[PARENT_ID] = O.[OBJECT_ID] WHERE O.[NAME] =@TNAME
INSERT INTO #TEMP
SELECT @TNAME,STUFF(@STR,1,2,'')

END

SELECT * FROM #TEMP
DROP TABLE #TEMP

END