Sign up ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Possible Duplicate:
How can I track database dependencies?
How can I know which store 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]
share|improve this question

marked as duplicate by gbn, Nick Chammas, jcolebrand Jan 17 '12 at 16:59

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

And too – gbn Jan 17 '12 at 15:39
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
@gbn thanks for those references, very helpful. Probably didn't find them to start with as was searching for 'dependancies' rather than 'dependencies' (both correct spellings I think) :) – Piers Myers Jan 17 '12 at 16:16
@JNK if you put your comment as an answer then I will have the opportunity to accept it. – Piers Myers Jan 17 '12 at 16:23
@PiersMyers - no need, I don't think it's a real complete answer - just a warning about the method you were using :) – JNK Jan 17 '12 at 16:25

Browse other questions tagged or ask your own question.