SQL Server 2000: Can't DROP VIEW? I have SQL Server 2000. I want to clean it by dropping obsolete and unused objects. One of the views causes me an error. I try to give more information but I still have no clue what the cause is.

Background: I found that one view can be drop and I know for sure no one uses it. And it links to detach (unused) database. I decided to drop it. Before I drop I check the view to make sure.

CREATE View vw_obsolete_view
AS
SELECT [very long],
,
,
...
FROM [unused_database].dbo.vw_obsolete_view  


DROP VIEW vw_obsolete_view


Now, View was dropped. It seem to be OK. However, I got a call from user that Application (ADP) got an error message. It states that view vw_obsolete_view is missing.

Since this view is not my coding and the my Application is call only my objects. So I have no idea why this cause an error. It took me an hour to figure out. I check all dependent objects. Nothing links to this view at all. I checked all ADP VBA code. Nothing relates to this view also.

I can't find what wrong with this. But I know that the problem is from dropping view because I was the only one who changes the database. So I make a new view with the same name.

CREATE View vw_obsolete_view
AS
SELECT 1 AS total


The error at the end user is disappear. End User is happy but I still can't drop view. I still have no clue. 

Sidenote: I can't upgrade SQL Server right now.
Run SQL Profiler to identify who, where etc. You have various columns with hostname, ntusername etc that will allow you to see usage.

For example: is if just one client or user who notices? Are they running a different ADP?

The view call can be embedded in some Form properties too as the RecordSource for that form, or as the source for some control. It may not be in VBA. You'd have to write some VBA to iterate through the forms, controls and reports testing assorted properties.