Views in Use?

Categories: News, Professional, Scripts, SSC, SSSOLV
Comments: 1 Comment
Published on: March 6, 2012

Today, I am following up on a topic I mentioned in an earlier post.  In case you haven’t read the post on finding your linked servers, you can read it here.

It is in that post that I spoke of altering a script I had just written for another client for another requirement.  The requirements were very similar in nature.

The client needed to discover which, if any, stored procedures in the database referenced any of the thousands of views that had been created.  (Spoiler – not a single view was being used by any stored procedure.)

My options at the time were to either write something that could do the work for me (rapidly) or manually investigate every stored procedure (there were thousands of those too).  I opted for the faster approach.

I already had a script handy to search for particular words or key phrases throughout all of the procs.  So, all I needed to do was work out the routine to search for all of the views.  The only approach I felt would work across the board was to write a cursor to do it.  For a job such as this, I think the tool is still appropriate.

Since you have already been exposed to a variation of the script, here is the version that works great for searching for the view usage in stored procs.  This script is simpler in nature than the one I shared in the Linked Server article – only because I do not query that SQL Agent jiobs for ad-hoc queries that use any views.

[codesyntax lang=”tsql”]



Put this in your toolbox if you like.  It did take it quite a while to run (20 or so minutes) when I ran it against a database with thousands of views and thousands of procs.  Just be warned .

1 Comment - Leave a comment

Leave a comment

Your email address will not be published. Required fields are marked *

March 2012
« Feb   Apr »


  • @G3lder: @PLucas87 Are you meaning the SCD task in SSIS, if so I'd recommend not using it as its shocking on performance! #Sqlhelp
  • @PLucas87: Anyone come across SCD treating derived columns as changes despite data being the same in SSIS 12? Errors on Fixed Atts #SSISHelp #SQLHelp
  • @JanosBerke: Anyone has experience (good or bad) w/ jTDS and connection pooling? #sqlhelp
  • @NaineshBabariya: #sqlhelp I want to move my distributor server to a brand new server. Can some one help me with the steps having minimal downtime.
  • @arcticdba: One table, 1M rows/day, ever-increasing BIGINT+other col as key. Needs to be stored for 10 years. Partitioning? Other ideas? #sqlhelp
  • @MrACutler: Can I swap package conns for project conns in SSIS 2014? Ive conv a project to project-deployment but can only do the first package #sqlhelp
  • @SQLHA: @SivassqlG Assuming FCI and one of 'em, why do you care about setting preferred owners (which is how it's done)? #sqlhelp
  • @SQLHA: @SivassqlG FCI? AG? SQL Server clustering is not specific enough. And one instance? More than one instance? #sqlhelp
  • @SivassqlG: #sqlhelp how to set node priority in SQL server clustering..means we have 3 node cluster active-A,passive-B,passive-C
  • @soteark: Datalength() on xml variable shows half the size of the same xml data in an xml file without formatting. Why and how to solve? #sqlhelp

Welcome , today is Friday, November 27, 2015