Hiiiiii
I want a qry which will give me the list of views which are not used in
any SPs in that database. like suppose i have a view view1 and I have
used that view in one of my SPs and i have a view called . View2 its
just created but never called in any SPs. Somebody ll help me ?

(reneeshprabha@gmail.com) writes:
> I want a qry which will give me the list of views which are not used in
> any SPs in that database. like suppose i have a view view1 and I have
> used that view in one of my SPs and i have a view called . View2 its
> just created but never called in any SPs. Somebody ll help me ?

That would be:

SELECT o.name
FROM sysobjects o
WHERE NOT EXISTS (SELECT *
FROM sysdepends d ON o.id = d.depid)
AND o.type = 'V'

However, be very very careful. If you recreate a view, all dependency
information is lost, and thus that view will appear as unused in this
query.

It may be better to script the stored procedures to a text file, and
then search the files for occurrences of the view name.


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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx