CODEWITHSUNDEEP
sqlsql-servertriggersdependencies
ABC
ABC

Reputation: 364

How do I get the table names referenced by a trigger in sql server?

I have a trigger with me and I want that which tables are being referenced by this trigger.Also same for Rules in sql server.I have a Rule in my db and I want the table name on which it is created. Also same for Indexes.

=========================EDIT===================

In case of following trigger Definition:

CREATE TRIGGER [dbo].[trgAfterInsert] ON dbo.[Employee_Test] 
FOR INSERT
AS
    declare @empid int;
    declare @empname varchar(100);
    declare @empsal decimal(10,2);
    declare @audit_action varchar(100);

    select @empid=i.Emp_ID from inserted i; 
    select @empname=i.Emp_Name from inserted i; 
    select @empsal=i.Emp_Sal from inserted i;   
    set @audit_action='Inserted Record -- After Insert Trigger.';

    insert into Employee_Test_Audit
           (Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp) 
    values(@empid,@empname,@empsal,@audit_action,getdate());

    PRINT 'AFTER INSERT trigger fired.'
GO

In this case 2 tables should be shown as dependent table which are Employee_Test and Employee_Test_Audit.How can I get these names?

Upvotes: 1

Views: 986

Answers (2)

Santhosh Rajoo
Santhosh Rajoo

Reputation: 79

You can try the below code : "sp_depends trigger name" This will provide all details like stored procedure called and user tables referenced by the trigger.

Upvotes: 0

Chanukya
Chanukya

Reputation: 5893

Try this one 

SELECT OBJECT_NAME(parent_id) AS tablename, *
FROM [databasename].sys.triggers

Upvotes: 1

Related Questions