Finding dependencies in SQL Server 2005

Question

Is there a dependable way to find dependencies among views and tables in SQL Server 2005? sys.sql_dependencies doesn't list all of my dependencies. (I thought I saw a similar thread here but can't find it now. Sorry if this is a dup).

Answer 1

Is this the thread you were looking for?

Answer 2

You can try these:

• How to write a query for SQL Server 2008 that returns the dependencies of an object (Exactly what Management Studio shows with the “View Dependencies” menu)
• T-SQL Puzzler - Crawling Object Dependencies
• In SQL Server, how do I identify all dependencies for a specific table using system tables/views?

Answer 3

No - SS 2005's dependency information is incomplete, that's why they introduced sql_expression_dependencies in 2008. If you're stuck on 2005, there's nothing you can really do, short of parsing all the objects yourself. There are extra tools that do this for you, have a look at the other dependency threads for links.

Answer 4

You have one main option only for code

select
    object_name(object_id), m.*
from
    sys.sql_modules m
where
    m.definition like N'%searchstring%'

syscomments and INFORMATION_SCHEMA.routines have nvarchar(4000) columns so may not be reliable

For all objects:

SELECT object_name(object_id), * FROM sys.sql_modules WHERE definition LIKE '%searchstring%'
UNION
SELECT object_name(object_id), * FROM sys.computed_columns WHERE definition LIKE '%searchstring%'
UNION
SELECT object_name(object_id), * FROM sys.check_constraints WHERE definition LIKE '%searchstring%'
UNION
SELECT object_name(object_id), * FROM sys.default_constraints WHERE definition LIKE '%searchstring%'