Too many stored procedures and views
-
Hi Everybody, I'm new to SQL Server and hope you can overlook my naivete...We have a database here which is used for various intranet applications (we develop with VB and .Net) and which now has over 1400 stored procedures and over 800 views. We suspect that many of these are not used at all anymore but we don't have complete documentation of our apps (are you surprised?!). Is there any way I could tell which stored procedures and views are actually used, if I were to monitor the database for a while? We would like to remove all unused objects (including tables) from this database just to clean it up and and make our documentation project easier. Thanks so much, Becky
-
maybe just creating a trigger on each view may help out.
create a new table to monitor all objects.
create table dbo.T_MyMonitorredObjects
(ObjectName varchar(256) not null primary key
, UsageCounter int not null default 0)
Insert into dbo.T_MyMonitorredObjects (ObjectName)
SELECT TABLE_SCHEMA + '.' + TABLE_NAME
FROM INFORMATION_SCHEMA.VIEWS
This trigger then could be like :
create trigger tr_IUD_myview
on myview -- owner + name
FOR INSERT , UPDATE , DELETE
as
Update dbo.T_MyMonitorredObjects
set UsageCounter = UsageCounter + 1
where ObjectName = 'myview'
Generate this trigger code for all objects you want to monitor.
Sprocs .... likewise. ?
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
That works for insert, update,delete, but not for select queries. Fro that you'd need to run Profiler and then examine the data. I'd recommend you do this for a few weeks, save off the files, check them daily or weekly.
Or you could purchase something like Log Explorer (), which would allow you to read back through log files and determine if there any users.
-
Oops. Overlooked the select
back to profiler then
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
And watch out for events that might only occur Monthly or Quarterly etc.
Andy. -
But Log Explorer will still not see Selects or Executions only logged events.
Your best bet is going to be Profiler in this situation.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply