Possible Duplicate:
How can I track database dependencies?
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]
  • And dba.stackexchange.com/q/972/630 too – gbn Jan 17 '12 at 15:39
  • 1
    Any way is better than this since syscomments has limitations - it only stores the first couple thousand characters of the definition of an object, so longer ones aren't checked completely. Sys.sql_modules is a better choice for that. – JNK Jan 17 '12 at 16:04

Browse other questions tagged or ask your own question.