Possible Duplicate:
How can I track database dependencies?How can I track database dependencies?
How can I know which stored procedure or trigger is using a table on SQL Server 2008 R2?How can I know which stored procedure or trigger is using a table on SQL Server 2008 R2?
I have been using the following SQL on SQL Server 2005 to find all the places where an object (table, stored procedure, view, function, etc) is used:
DECLARE @search_string NVARCHAR(max)
SET @search_string = 'foobar'
SELECT DISTINCT SCHEMA_NAME(so.[schema_id]) + '.' + so.[name] AS [Used By],
so.[type_desc] as [Type]
FROM [sys].[syscomments] sc
INNER JOIN [sys].[objects] so ON sc.[id] = so.[object_id]
WHERE sc.[text] like '%[~[. ]' + @search_string + '[~]. ]%' ESCAPE '~'
ORDER BY [Used By]
Are there any better ways of doing this?
Update, with advice from @JNK, I have changed it to use sql_modules rather than syscomments:
DECLARE @search_string NVARCHAR(max)
SET @search_string = 'eligibility'
SELECT DISTINCT SCHEMA_NAME(o.[schema_id]) + '.' + OBJECT_NAME(sm.[object_id]) AS [Used By],
o.[type_desc] AS [Type]
FROM [sys].[sql_modules] AS sm
INNER JOIN [sys].[objects] AS o ON sm.[object_id] = o.[object_id]
WHERE sm.[definition] LIKE '%[~[. ]' + @search_string + '[~]. ]%' ESCAPE '~'
ORDER BY [Used By]
