Views - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Views

Author

Topic

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2011-11-22 : 10:40:26

How to check if the views on the databases are not being used since given period of time.

Thanks for your help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-22 : 11:14:21

http://www.sqlmag.com/blog/practical-sql-server-45/sql-server-2008-r2/finding-potentially-unused-views-databases-140294

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2011-11-22 : 12:33:56

Thanks Visakh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-22 : 12:38:46

np

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

johntech
Yak Posting Veteran

51 Posts

Posted - 2011-11-23 : 11:32:41

http://www.scoop.it/t/sql-server/p/397146959/finding-potentially-unused-views-in-your-databases-sql-server-magazine-blog

USE <dbname>
GO

SELECT v.name [ViewName]
FROM sys.views v
WHERE v.is_ms_shipped = 0

EXCEPT SELECT o.Name [ViewName]
FROM master.sys.dm_exec_cached_plans p
CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) t
INNER JOIN sys.objects o ON t.objectid = o.object_id
WHERE t.dbid = DB_ID()
GO

And, again, those views aren’t necessarily non-used. They’re just views that aren’t in the plan cache – which makes it easier for me to know which views to focus my time on (rather than trying to script/ALTER them all).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources