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”]
Declare @VName varchar(256)
Declare FindView cursor
LOCAL STATIC FORWARD_ONLY READ_ONLY
FOR
Select name
From sys.objects
Where type = 'v'
And is_ms_shipped = 0
Open FindView;
Fetch next from FindView into @VName;
while @@FETCH_STATUS = 0
Begin
SELECT OBJECT_NAME(object_id)
FROM sys.sql_modules
WHERE Definition LIKE '%'+@VName +'%'
AND OBJECTPROPERTY(object_id, 'IsProcedure') = 1 ;
Fetch next from findview into @VName;
End
Close FindView
Deallocate FindView[/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 .
