2

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
yogibear
  • 14,487
  • 9
  • 32
  • 31

4 Answers4

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>');
Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
  • This sys.sql_dependencies îs deprecated in later versions + is it reliable? See my answer please... – gbn Oct 18 '09 at 12:37
  • sysdepends is the deprecated one. sql_dependencies was introduced in 2005 and afaik is not deprecated. Aaron articles goes over the various issues in more depth than I could comment here. My opinion is that metadata dependency tracking is too fragile and the good practice is to track dependencies at the development source control level. Eg. VSTS Database Edition GDR R2 does a better job at this: http://www.microsoft.com/downloads/details.aspx?FamilyID=bb3ad767-5f69-4db9-b1c9-8f55759846ed&displaylang=en. – Remus Rusanu Oct 18 '09 at 17:36
4

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.

gbn
  • 422,506
  • 82
  • 585
  • 676
1

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

Community
  • 1
  • 1
Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
0

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' ;
dlamblin
  • 43,965
  • 20
  • 101
  • 140