refreshview removes user defined functions from sysdepends

When I create a procedure that references a user defined procedure it
appears in sysdepends, but disapears after being refreshed. Is this
expected and is there an alternative to sp_refreshview that correctly
refreshes sysdepends
--0--0--
print 'drop depenencies'
go
drop view depends_test
go
print 'create view'
go
create view depends_test as
select
dbo.ufJsmTranslate('test') As test_column
from
(select 1 one) test
go
print 'check depenencies'
go
sp_depends depends_test
go
print 'refresh'
go
sp_refreshview depends_test
go
print 'check depenencies'
go
sp_depends depends_test
--0--0--
drop depenencies
create view
check depenencies
In the current database, the specified object references the following:
name type updated selected column
-- -- -- -- --
dbo.ufJsmTranslate scalar function no no
refresh
check depenencies
Object does not reference any object, and no objects reference it.
--0--0--May be this is a bug, It seems that sp_refreshview does not update reference
s
to udfs. Here I have a script with the same problem. I also tried to create
the udf and view with the SCHEMABINDING property, everything was ok, except
that sp_refresh is giving me an error when I try to refresh the view.
-- same problem
use northwind
go
create table t(colA int)
go
create function dbo.ufn_funct1 (
@.i int
)
returns int
as
begin
return (@.i)
end
go
create view myview
as
select dbo.ufn_funct1(colA) as colA from t
go
exec sp_depends myview
go
exec sp_refreshview myview
go
exec sp_depends myview
go
drop view myview
go
drop function dbo.ufn_funct1
go
drop table t
go
-- Tried to fix it using schemabinfing
-- sp_refreshview is giving me an error
use northwind
go
create table t(colA int)
go
create function dbo.ufn_funct1 (
@.i int
)
returns int
with schemabinding
as
begin
return (@.i)
end
go
create view dbo.myview
with schemabinding
as
select dbo.ufn_funct1(colA) as colA from dbo.t
go
exec sp_depends 'dbo.myview'
go
exec sp_refreshview 'dbo.myview'
go
-- Server: Msg 208, Level 16, State 8, Procedure sp_refreshview, Line 1
-- Invalid object name 'dbo.myview'.
exec sp_depends 'dbo.myview'
go
drop view dbo.myview
go
drop function dbo.ufn_funct1
go
drop table t
go
AMB
"bilbo.baggins@.freesurf.ch" wrote:

> When I create a procedure that references a user defined procedure it
> appears in sysdepends, but disapears after being refreshed. Is this
> expected and is there an alternative to sp_refreshview that correctly
> refreshes sysdepends
> --0--0--
> print 'drop depenencies'
> go
> drop view depends_test
> go
> print 'create view'
> go
> create view depends_test as
> select
> dbo.ufJsmTranslate('test') As test_column
> from
> (select 1 one) test
> go
> print 'check depenencies'
> go
> sp_depends depends_test
> go
> print 'refresh'
> go
> sp_refreshview depends_test
> go
> print 'check depenencies'
> go
> sp_depends depends_test
> --0--0--
> drop depenencies
> create view
> check depenencies
> In the current database, the specified object references the following:
> name type updated selected column
> -- -- -- -- --
> dbo.ufJsmTranslate scalar function no no
> refresh
> check depenencies
> Object does not reference any object, and no objects reference it.
> --0--0--
>|||Correction,

> May be this is a bug, It seems that sp_refreshview does not update referen
ces
> to udfs. Here I have a script with the same problem. I also tried to creat
e
> the udf and view with the SCHEMABINDING property, everything was ok, excep
t
> that sp_refresh is giving me an error when I try to refresh the view.
..., except that sp_refreshview ...

> -- Server: Msg 208, Level 16, State 8, Procedure sp_refreshview, Line 1
> -- Invalid object name 'dbo.myview'.
> exec sp_depends 'dbo.myview'
> go
-- Server: Msg 208, Level 16, State 8, Procedure sp_refreshview, Line 1
-- Invalid object name 'dbo.myview'.
exec sp_refreshview 'dbo.myview'
go
AMB
"Alejandro Mesa" wrote:
> May be this is a bug, It seems that sp_refreshview does not update referen
ces
> to udfs. Here I have a script with the same problem. I also tried to creat
e
> the udf and view with the SCHEMABINDING property, everything was ok, excep
t
> that sp_refresh is giving me an error when I try to refresh the view.
> -- same problem
> use northwind
> go
> create table t(colA int)
> go
> create function dbo.ufn_funct1 (
> @.i int
> )
> returns int
> as
> begin
> return (@.i)
> end
> go
> create view myview
> as
> select dbo.ufn_funct1(colA) as colA from t
> go
> exec sp_depends myview
> go
> exec sp_refreshview myview
> go
> exec sp_depends myview
> go
> drop view myview
> go
> drop function dbo.ufn_funct1
> go
> drop table t
> go
> -- Tried to fix it using schemabinfing
> -- sp_refreshview is giving me an error
> use northwind
> go
> create table t(colA int)
> go
> create function dbo.ufn_funct1 (
> @.i int
> )
> returns int
> with schemabinding
> as
> begin
> return (@.i)
> end
> go
> create view dbo.myview
> with schemabinding
> as
> select dbo.ufn_funct1(colA) as colA from dbo.t
> go
> exec sp_depends 'dbo.myview'
> go
> exec sp_refreshview 'dbo.myview'
> go
> -- Server: Msg 208, Level 16, State 8, Procedure sp_refreshview, Line 1
> -- Invalid object name 'dbo.myview'.
> exec sp_depends 'dbo.myview'
> go
> drop view dbo.myview
> go
> drop function dbo.ufn_funct1
> go
> drop table t
> go
>
> AMB
>
> "bilbo.baggins@.freesurf.ch" wrote:
>