September 19, 2001 at 1:33 pm
Here is a view declaration:
CREATE VIEW dbo.AdOrderTotalView
AS
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.
Thanks.
September 19, 2001 at 5:55 pm
I created a view in Northwind as follows:
CREATE VIEW dbo.vTest
AS
SELECT dbo.Categories.*
FROM dbo.Categories
GO
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.
Andy
September 20, 2001 at 7:05 am
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.
Thanks.
Damir
September 20, 2001 at 7:39 am
It is me again.
To see what am I talking about please run this query:
select left(a.name,40), left(c.name,40) from sysobjects a
join sysdepends b on a.id = b.id
left join sysobjects c on b.depid = c.id
where a.xtype = 'v' and c.name is null
Thanks.
Damir
September 20, 2001 at 8:29 am
Returns nothing. Why would you have an object (c.name) that was null??
Andy
September 20, 2001 at 9:12 am
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.
Damir
December 8, 2002 at 5:37 pm
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.)
December 8, 2002 at 6:47 pm
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