mardi 14 juin 2016

Find a view dependency at any db

I'm in the middle of cleaning some servers. So currently this is my server (sorry for the lame diagram):

DatabaseServer00
 -DatabaseA
   -ViewA
   -ViewB
   -ViewC
 -DatabaseB 
 -DatabaseC

I need to find if there is any sp/triger/function/etc... in DatabaseB/C that calls ViewA from DatabaseA.

I know that i can find dependencies in DatabaseA with this sp_depends And I'm currently using this to search in other DB

    Declare @Query varchar(max)
    SET @Query = 'SELECT DISTINCT o.name AS Object_Name,o.type_desc FROM
    sys.sql_modules m INNER JOIN sys.objects  o ON m.object_id=o.object_id
    WHERE m.definition like ''%ViewA%''' 
    EXEC sp _MSforeachdb @Query

But.... This code right here ^ does not returns me that ViewB depends on ViewC

So i'm kind of out of queries to run. Thanks in advance for your help.

Aucun commentaire:

Enregistrer un commentaire