How can I find thread dependencies for Views?
In SQL Server 2005, you should find the dependencies in Manglement Studio by right-clicking the object and choosing View Dependencies. In the dialog box, you can select "Objects that depend on this" (what I call down-stream) or "Objects that depend on" (up-stream).
Both directions seem to work adequately for Table objects, and View objects seem to communicate good information up-and-down. However, the downstream list for the view appears to only consist of the view itself - even when I know there are other dependents (different view in this case).
Is there a way to find this information? It is convenient for me to write queries to system tables if I have a targeting key ...
a source to share
try:
sp_depends YourViewName
if you don't get any results, please clear and re-create the view and try again. Rollback and recreate may work for the GUI, but I didn't try there
this is a bit slow (and not the best query), but try:
DECLARE @Search varchar(300)
SET @Search='yourViewName'
SELECT DISTINCT
LEFT(so.name, 120) AS Object_Name,
"object_type"=left(
case so.type
when 'U' then 'Table - User'
when 'S' then 'Table - System'
when 'V' then 'Table - View'
when 'TR' then 'Trigger'
when 'P' then 'Stored Procedure'
when 'C' then 'Constraint - Check'
when 'D' then 'Default'
when 'K' then 'Key - Primary'
when 'F' then 'Key - Foreign'
when 'L' then 'Log'
when 'R' then 'Rule'
when 'RF' then 'Replication Filter stp'
else '<<UNKNOWN '''+so.type+'''>>'
end -- case so.type
,50)
FROM syscomments sc
INNER JOIN sysobjects so
ON so.id = sc.id
WHERE
text Like '%'+@Search+'%'
ORDER BY
2,1
a source to share