Views in Use?

Categories: News, Professional, Scripts, SSC, SSSOLV
Comments: No Comments
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"]

[/codesyntax]

 

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 .

No Comments - Leave a comment

Leave a comment

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">






Calendar
March 2012
M T W T F S S
« Feb   Apr »
 1234
567891011
12131415161718
19202122232425
262728293031  
Content
SQLHelp

SQLHelp


Welcome , today is Wednesday, July 30, 2014