[dba-SQLServer] Can't drop view

jwcolby jwcolby at colbyconsulting.com
Fri Jun 5 13:45:02 CDT 2009


I have a stored procedure that drops and dynamically rebuilds a view.  This stored procedure 
suddenly fails.  It turns out that the view that the SP was trying to drop referenced a non-existent 
table.  I could not even "compile" the sp.  BTW, what do you call the execute phase when you are 
editing a stored procedure - as opposed to actually running the stored procedure using EXEC()?

Anyway, when I tried to perform that step it gave me error messages.  Apparently the SP tries to 
open the view and if the view is bad it barfs.  From my perspective, I don't care if the view is bad 
because I am going to drop the view anyway and rebuild it from scratch.  Unfortunately if the SP 
won't run then I can't drop the bad view.  Not good.

So I went in and manually deleted the view and the stored procedure runs as I intended.

Is there any way to programmatically drop a view that references a non-existent table, or has any 
other error that would prevent the view from running?

-- 
John W. Colby
www.ColbyConsulting.com



More information about the dba-SQLServer mailing list