|
|
Posted by Erland Sommarskog on 11/08/06 23:12
(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/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|