sp_refreshview

From: Ray DeCampo (rkdecampo_at_promergent.com)
Date: 07/07/04 

Date: 7 Jul 2004 07:56:57 -0700

Hello,

I have encountered what seems to be a bug with the sp_refreshview
procedure. The issue is when one view depends on another, if
sp_refreshview is executed on the independent view, the dependency is
stricken from the metadata.

You can see this behavior for yourself by executing the following:

--- Begin script
USE pubs
GO

CREATE VIEW authors_view AS SELECT au_lname, au_fname FROM authors
GO

CREATE VIEW ref_authors_view AS SELECT au_lname, au_fname FROM
authors_view
GO

SELECT * FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE
GO

sp_refreshview 'authors_view'
GO

SELECT * FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE
GO

sp_refreshview 'ref_authors_view'
GO

SELECT * FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE
GO
--- End script

The results of the first query correctly show the relationship between
the ref_author_view and the author_view. The second query however
does not show this relationship after sp_refreshview was executed on
the authors_view view. The third query shows the meta-data has been
restored.

Has anybody encountered this issue before and how did you handle it?
Note that I am trying to determine the dependencies between views in
an arbitrary unknown database. So it is not an option to execute
sp_refreshview in the "correct" order, since if I knew the correct
order I wouldn't need to execute it at all.

Thanks,
Ray DeCampo

Relevant Pages

  • Re: Finally which ORM tool?
    ... manipulate the linq query IF you're executing it at that moment. ... simply because the declaration construction was with 'CHOPS'. ... implement IEnumerablebut had an Execute() method which gave back ...
    (microsoft.public.dotnet.languages.csharp)
  • RE: SQL stored procedure executing twice
    ... I wasn't aware that DLookupwould execute the "domain" more than once. ... caused the stored procedure to execute twice. ... Dim stDocName As String ... My pass-thru query properties ...
    (microsoft.public.access.modulesdaovba)
  • Re: SQL Insert help
    ... Where, exactly, are you placing your query syntax? ... example) where it could by grabbed by the query script? ... Execute SQL script function simply uses the global field as its query. ...
    (comp.databases.filemaker)
  • Re: Clarification on DBI module
    ... You get the same results for the second execute because the record buffer in the statment handle is not cleaned out because no execute took place. ... The sql query can have bind variables or they may not have. ... Ahhh...imagining that irresistible "new car" smell? ...
    (perl.dbi.users)
  • RE: Running action function from SELECT query
    ... Yes ADO can execute queries saved in an Access database. ... I do not know how ADO.NET works, but the developer tells me he is able to ... At any rate, I can call just a function from a query, like this: ...
    (microsoft.public.access.modulesdaovba)