Posted by Erland Sommarskog on 07/14/05 01:00

David Portas (REMOVE_BEFORE_REPLYING_dportas@acm.org) writes:
> No error will be generated when you drop a table referenced in a view
> unless the view is indexed. Maybe you can index your view(s), although
> in my opionion you shouldn't need to do so just for this. Surely you
> wouldn't drop a table in production without first testing your code? If
> the table or view is used in code then testing will show an error. If
> the table or view isn't used then it isn't relevant. MS could have
> implemented it better but unfortunately they didn't.

You don't need to go all the way and index the view. As Dan said,
what you need is WITH SCHEMABINDING. Try this:


CREATE TABLE mytable (a int NOT NULL)
go
CREATE VIEW myview WITH SCHEMABINDING AS
SELECT a FROM mytable
go
DROP TABLE mytable -- Fails

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]