February 25, 2009 at 2:31 pm
I have posted before on this idea, to be able to find those views/tables which have not been
used in an X period of time to mark them for deletion. So I know there is no SQL command to
do this. The IT group is still insisting on a way to find it to clean up the server. (Why? Well
I asked that, they seem to think lots of views means performance issues. I researched and know
it has no real impact. Fell on deaf ears however.)
So... I still need to manage to pull off something for this. I have built a stored proc to
accomplish finding those run dates, but I am not sure if there are any issues I have not
considered, and would love some input.
I created a stored proc, which will take the name of a view or table as the parameter. Inside
the proc I use the view/table name to create a SQL command. I use the parameter to look in the
sys tables to actually build a full statement not a select * sort of thing.
I then place the view name, user ID and date/time into a tracking table.
The idea seems too "simple" for me to think I have not missed something. I don't see it
as the sort of dynamic SQL which would be prone to Injection attacks, as the only parameter
is used as a criteria in a parameterized query.
Backgroud
This is for adhoc and simple reports (those without parameters, or just data dumps).
Users do not write their own queries, all data requests come through my team.
We use Excel as the display front end, and call the stored proc in MS Query.
Security is windows authentication, each user has their own id.
The stored proc is executable by all, the views/tables have security on them. In tests,
the security flows through as intended.
What am I missing? :crazy:
February 26, 2009 at 6:57 am
Yes, this would work, but you would have some overhead creating the query and you also, depending on your implementation, open yourself up to SQL Injection.
Couldn't you set up a server trace that tracks queries and then you could query the trace for tables/views accessed? If you put a filter on DatabaseID it would probably add less overhead than dynamically creating queries each time.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 26, 2009 at 9:01 am
Thanks Jack!
Good points, I will have to look into the trace idea. I have not had cause yet to learn much about traces, time to learn now.
It is a darn good thing I enjoy the digging and learning, SQL is great for always having another thing to learn.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply