Dependecies Question

  • Here is a view declaration:

    CREATE VIEW dbo.AdOrderTotalView


    SELECT AdOrderID, Total = SUM(LineAmt)

    FROM AdOrderDet

    GROUP BY AdOrderID

    The problem is that call to "sp_depends 'adordertotalview'"

    doesn't return anything.

    Does anybody know why? Or maybe I just don't understand dependencies.


  • I created a view in Northwind as follows:

    CREATE VIEW dbo.vTest


    SELECT dbo.Categories.*

    FROM dbo.Categories


    Then ran:

    sp_depends 'vtest'

    Which returned:

    dbo.Categoriesuser tablenoyesPicture

    dbo.Categoriesuser tablenoyesrowguid

    dbo.Categoriesuser tablenoyesTestCol

    dbo.Categoriesuser tablenoyesCategoryID

    dbo.Categoriesuser tablenoyesCategoryName

    dbo.Categoriesuser tablenoyesDescription

    That matches the 6 columns in my Categories table. Are you sure you are executing sp_depends in the same db where you created the view? It won't report external dependencies. You can also look at the TSQL source to sp_depends in the master and even step through in the debugger if you're still stuck.


  • Hi Andy,

    thanks for answer.

    In meantime in some Microsoft public newsgroups I found some interesting discussions about this problem. It just seems that sysdepends table is not updated if you drop and re-create the object (in my case adorder table). I checked my sysdepends for object 'adordertotalview'. In 'depid' field it shows id of nonexisting object.

    Do you maybe know any (easy) way to 'refresh' sysdepends table.



  • It is me again.

    To see what am I talking about please run this query:

    select left(,40), left(,40) from sysobjects a

    join sysdepends b on =

    left join sysobjects c on b.depid =

    where a.xtype = 'v' and is null



  • Returns nothing. Why would you have an object ( that was null??


  • Nothing? In my case returns lot of rows. It is supposed to return all entries from sysdepends where depid contains id of non-existing object.

    Maybe something’s wrong with my database.

    Anyway, thanks for reply.


  • EM drops and recreates objects when you use it just to edit a view or proc. Once an object is created, use ALTER to change its contents and not lose the dependencies tracked with it. You can also include "with schemabinding" to ensure the object is considered when its base objects are modified. (ie. you can't change them until you remove schemabinding from the dependent objects.)

  • sp_refreshview

    From BOL

    Refreshes the metadata for the specified view. Persistent metadata for a view can become outdated because of changes to the underlying objects upon which the view depends.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply