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"]

[/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 .

1 Comment - 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

  • @SQLSoldier: @SQLDBA @SQLYard If not connected to the internet, will need to download manually and specify an alternate path for it. #sqlhelp
  • @SQLDBA: @SQLYard That's .NET framework 3.5. Enable through Windows features. #sqlhelp
  • @SQLYard: #sqlhelp, Issue installing SQL on a VM 2012 Please try enabling Windows feature :NetFx3. I have tried most of the fixes on the web.Thanks
  • @sqlconcepts: Dear #sqlhelp, does anyone have a bit of code that will help me estimate the space requirement for Change Tracking on a table?
  • @DevDBABrian: @SQLPoolBoy #SQLHelp Thank you Jonathan. SSMS wizard be good.
  • @SQLPoolBoy: @DevDBABrian Or if it needs to be up to date near real time look at replication to the other location #sqlhelp All depends on requirement
  • @SQLPoolBoy: @DevDBABrian For just a one time copy of the data Import/Export wizard in SSMS #sqlhelp Save the package for routine use if necessary
  • @DevDBABrian: Need to copy 20 tables (5GB) to another server/db to support a monthly process. What approach do you recommend? #sqlhelp
  • @ChrisAdkin8: @MladenPrajdic you have to admire their tanacity, my #sqlhelp question memory grants in execution plans in mamagement studio are in bytes ?
  • @ChrisAdkin8: #sqlhelp when you mouse over the left most node in a plan that has granted memory the memory grant unit is in bytes ?

Welcome , today is Tuesday, September 30, 2014