SQL Server 2005 search views for certain database

Question

Is there a way in SQL Server to list all the views within a database that join from a particular object?

ie: find all the views that join from the table myTable

Answer 1

You have to search code and you have two options only. See comments below why other methods are not reliable.

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

or use OBJECT_DEFINITION

syscomments and INFORMATION_SCHEMA have nvarchar(4000) columns. So if "myViewName" is used at position 3998, it won't be found. syscomments does have multiple lines but ROUTINES truncates.

In SQL Server 2000, the sys.depends was unreliable, which affected "View dependencies" in the menus. I don't know if it's better in SQL 2005+. Example: view uses table, table is dropped and recreated, has different objectid, dependency = broken.

Answer 2

Here's some of the examples from the link provided by unknown (in case that site disappears before stackoverflow does)

For SQL Server 2005, right click on the table name and select "View Dependencies"

A couple of the SQL only methods mentioned for SQL Server:

SELECT routine_name, routine_type FROM INFORMATION_SCHEMA.ROUTINES
  WHERE ROUTINE_DEFINITION LIKE '%Employee%'

EXEC sp_depends @objname = N'HumanResources.Employee' ;

Answer 3

In SQL Server 2005, you can use a combination of sys.sql_dependencies and brute force parsing of the object text (as in gbn's answer). For more info on SQL Server 2005 dependencies, see http://msdn.microsoft.com/en-us/library/ms345449(SQL.90).aspx

In SQL Server 2008, there are new dependency DMVs and catalog views that are a bit more trustworthy than previous methods (sys.dm_sql_referenced_entities / sys.dm_sql_referencing_entities / sys.sql_expression_dependencies), but it is still easy to break them.

See this article for some ideas to make this work better. Also see http://msdn.microsoft.com/en-us/library/bb677168.aspx

Answer 4

You can use sys.sql_dependencies:

select object_name(object_id),* 
from sys.sql_dependencies
where referenced_major_id = object_id('<tablename>');

This will list all objects that depend on your table, you can restrict this to views by joining against sys.views:

select v.* 
from sys.sql_dependencies d
join sys.views v on d.object_id = v.object_id
where referenced_major_id = object_id('<tablename>');