How to make sure views are up to date

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to make sure views are up to date

Author

Topic

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2008-06-26 : 12:31:39

Greetings

I have come across a scary feature of SQL 2005 that I should have been more careful about. When table changes happen the views do no necessarily reflect that. so for example if I have vwJobOperators based on JobOperator table which has following structure
JobOperator
JobOperatorID
JobName
JobDescr
JobFinishedYesNo
JobStartDateTime
JobStartEndTime

No let say JobOperator table changes and I remove JobDescr field. But somewhere out there (if love can see us through, then we'll be together somewhere out there, out where dreams come true..oops sorry got carried away there)
a stored procedures says .... SELECT ... WHERE JobDescr NOT IN (SELECT JobDescr FROM vwJobOperators ) no error happens everything works just fine except it is not you get back wrong results.
So to get to my point how do you write stuff so that views are rebuilt on the fly when being called bu other stored procedures or whatever calls them so you can fix this problem.

HELP!

THanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-26 : 12:52:23

We just search through our code for whatever we've removed. You can search through syscomments easily, your source control files, or via a script like Vyas has on his site.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-26 : 12:53:37

You need manually write code to look for them using INFORMATION_SCHEMA.VIEW_TABLE_USAGE catalog view information.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-06-26 : 13:00:28

Tara,Visakh
Would schema-binding be appropriate here?

Jim

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-26 : 13:04:06

quote:
Originally posted by jimf

Tara,Visakh
Would schema-binding be appropriate here?

Jim

Schema binding will only ensure that under table schema cannot be changed but it wont recreate the views automatically when you want to change base table structures. It wont let you change them at all!

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-06-26 : 13:15:41

That's what I meant, to prevent the situation from happening in the first place. You can mess up a lot of people by changing the table structure first and then listening to hear who screams!

Jim

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-26 : 13:19:29

quote:
Originally posted by jimf

That's what I meant, to prevent the situation from happening in the first place. You can mess up a lot of people by changing the table structure first and then listening to hear who screams!

Jim

Yeah...as a policing measure you can very well do that. But i was answering it from OP's point of view i.e having created a view without specifying schema binding how can be make sure view gets recreated to reflect changes in base tables.