SQL Server 2000: Can't DROP VIEW?

Question

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.

Answer 1

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.

Answer 2

Besides running a SQL Server Profiler trace using the Standard (default) template + a filter on the TextData column you could also try to check if the view is a dependency of any other object from inside the database:

-- not always up to date:
EXEC sp_depends 'vw_obsolete_view'         

-- undocumented:
EXEC sp_MSdependencies 'vw_obsolete_view'  

-- just SPs and functions:
SELECT routine_name, routine_type
FROM INFORMATION_SCHEMA.ROUTINES 
WHERE ROUTINE_DEFINITION LIKE '%vw_obsolete_view%'

-- syscomments can contain multiple lines per object, so the following is prone to error:
select * 
from syscomments sc
    join sysobjects so on so.id = sc.id
where sc.text like '%vw_obsolete_view%' 

Even if it's used from inside the database it might be used from some dynamic SQL phrase that doesn't show up on the above searches, so the best bet is still on SQL Server Profiler.