how to locate a table that contains a specific trigger?

Hello,
the following tsql
select DISTINCT OBJECT_NAME([id]) FROM sysdepends
WHERE OBJECT_NAME([depid]) = 'tbl_CompanyChangeHistory'
displays a list of UDFs and a trigger that tbl_CompanyChangeHistory is
dependent upon. I need to locate the table that contains this specific
trigger.
I tried
select DISTINCT OBJECT_NAME([id]) FROM sysdepends
WHERE OBJECT_NAME([depid]) like 't_ChgHistory_Write%'
but this did not yield anything (empty).
How can I locate the table which contains this trigger?
Thanks,
RichReverse id and depid:
select DISTINCT OBJECT_NAME([depid]) FROM sysdepends
WHERE OBJECT_NAME([id]) like 't_ChgHistory_Write%' --trigger name?
"Rich" wrote:

> Hello,
> the following tsql
> select DISTINCT OBJECT_NAME([id]) FROM sysdepends
> WHERE OBJECT_NAME([depid]) = 'tbl_CompanyChangeHistory'
> displays a list of UDFs and a trigger that tbl_CompanyChangeHistory is
> dependent upon. I need to locate the table that contains this specific
> trigger.
> I tried
> select DISTINCT OBJECT_NAME([id]) FROM sysdepends
> WHERE OBJECT_NAME([depid]) like 't_ChgHistory_Write%'
> but this did not yield anything (empty).
> How can I locate the table which contains this trigger?
> Thanks,
> Rich
>|||Thanks for your reply. I had the [id] and [depid] columns switched around
which was incorrect. But your solution retrieved the dependent table and a
dependent UDF (still good stuff).
Here is something else I tried that actually retrieve the table I was
looking for
select OBJECT_NAME([parent_obj]) FROM sysobjects
WHERE [id] = 1467920351
The [id] here is the [id] of the trigger which I retrieved like this:
select * from sysobjects where xtype ='tr'
and name = 't_CompaniesWrite'
"Mark Williams" wrote:
> Reverse id and depid:
> select DISTINCT OBJECT_NAME([depid]) FROM sysdepends
> WHERE OBJECT_NAME([id]) like 't_ChgHistory_Write%' --trigger name?
>
> --
>
> "Rich" wrote:
>|||Rich
I'd not rely on sysdepends table ,instead take a look at Vyas's examle
CREATE PROCEDURE sp_FindObject
@.SearchString varchar (255)
AS
SET nocount ON
DECLARE @.Name varchar(255)
DECLARE @.Text nvarchar(4000)
CREATE TABLE #Objs
( ObjName varchar (255))
DECLARE Obj CURSOR
FOR
SELECT [NAME]
FROM
(
SELECT [NAME],[TEXT] FROM sysobjects so, syscomments sc
WHERE (so.xtype ='P' )
AND so.id = sc.id
UNION ALL
SELECT [NAME],[TEXT] FROM sysobjects so, syscomments sc
WHERE (so.xtype ='V' )
AND so.id = sc.id
UNION ALL
SELECT [NAME],[TEXT] FROM sysobjects so, syscomments sc
WHERE (so.xtype ='TR' )
AND so.id = sc.id
) AS Der WHERE [TEXT] LIKE @.SearchString
OPEN Obj
FETCH Next FROM Obj INTO @.Name
WHILE @.@.FETCH_STATUS=0
BEGIN
INSERT INTO #Objs VALUES (@.Name)
FETCH Next FROM Obj INTO @.Name
END
CLOSE Obj
DEALLOCATE Obj
SELECT objname FROM #Objs GROUP BY objname
DROP TABLE #Objs
GO
EXEC sp_FindObject '%HOST_ID()%'
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:BF61EDA5-4130-40E8-8FA5-2DF3938B0E9C@.microsoft.com...
> Thanks for your reply. I had the [id] and [depid] columns switched around
> which was incorrect. But your solution retrieved the dependent table and
> a
> dependent UDF (still good stuff).
> Here is something else I tried that actually retrieve the table I was
> looking for
> select OBJECT_NAME([parent_obj]) FROM sysobjects
> WHERE [id] = 1467920351
> The [id] here is the [id] of the trigger which I retrieved like this:
> select * from sysobjects where xtype ='tr'
> and name = 't_CompaniesWrite'
> "Mark Williams" wrote:
>