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 *










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

SQLHelp

  • @Ko_Ver: @NickyvV no, no special tricks in the package :) #sqlhelp
  • @NickyvV: @Ko_Ver I've seen that last week that the Job (step) succeeded, but it actually failed with an error #sqlhelp
  • @NickyvV: @Ko_Ver Is FailParentOnFailure set to false on a task/container maybe? #sqlhelp
  • @Ko_Ver: When I start an Agent job, the "job running" pop-up says there's an error. But SSIS catalog and Agent history says jobs succeed. #sqlhelp
  • @DbaMayor: #sqlhelp I'm seeing LCK_M_U and LCK_M_RS_U waits on sec readonly replica with only reporting queries...anyone faced similar issue?..thnks
  • @TrustMeImADBA: Prob not a good question for #sqlhelp but what is the chances time-service changing the time 23 sec forward made my sql cluster failover?
  • @zippy1981: Seem to be that I need to install SSMS vNext Verifying now #sqlhelp
  • @ChrisAdkin8: @YaHoznaDBA you should also compare avg writelog waits to avg tran log write stall,the two are not necessarily foced to be the same #sqlhelp
  • @toddkleinhans: #sqlhelp Will try using lock priority. Totally forgot about that. Thanks!
  • @DBArgenis: @toddkleinhans Compatibility mode is for T-SQL constructs and Query Optimization, nothing else. #sqlhelp

Welcome , today is Tuesday, February 21, 2017