| Author |
Topic |
|
Bredsox
Starting Member
4 Posts |
 Posted - 2005-06-22 : 12:04:28
|
| Hello Everyone, Could anyone please help me to write a stored procedure that resides in the Master Database. This Stored Procedure should be able to query all the databases in SQL Server and deletes all temporary objects it finds from the server. Thank you in advance for your help!Regards,Dan |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-06-22 : 12:47:00
|
| How do you define what a "temporary object" is? How do you know something is not using it?Be One with the OptimizerTG |
 |
|
|
nathans
Aged Yak Warrior
938 Posts |
Posted - 2005-06-22 : 12:58:15
|
| -- edit: TG is on it :)You could create a dynamic statement to execute the drop command against all user tables in the tempdb, but I am curious why you have rogue temp objects. How will you distinguish between "in-use" objects and others?? |
|
|
|
TimS
Posting Yak Master
198 Posts |
Posted - 2005-06-22 : 13:26:18
|
Let's use a cursor to do it. If it all goes right/wrong you could end up with a lock on all the temp tables so the whole server crashes.Tim S |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2005-06-22 : 13:53:56
|
| What do you mean by temporary objects?I think you might mean Work objects.Temp tables have a whole different meaning, and are only around for the length of the spid.Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx |
|
|
|
Bredsox
Starting Member
4 Posts |
Posted - 2005-06-22 : 14:57:10
|
| Thanks everyone for the prompt response. I was refering to Unused, old tables or objects (that are no longer needed) which needs to be identified by the stored procedure that I write. Sorry for the confusion.ThanksDan |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-06-22 : 15:03:21
|
and how do you know which tables or objects are no longer needed?Go with the flow & have fun! Else fight the flow  |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-06-22 : 15:06:35
|
| Are you looking for code that performs the deletes/drops or code that identifies which objects are no longer needed?Be One with the OptimizerTG |
|
|
|
Bredsox
Starting Member
4 Posts |
Posted - 2005-06-22 : 15:09:27
|
| TG, the code that identifies which objects are no longer needed. Once I get this identified, Probablt i can delete or drop the objects with SPs.Thanks Dan |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-06-22 : 15:13:10
|
| If you're not explicitly tracking usage and dependencies that can be difficult to determine. (and risky) However you end up doing it, make sure you've got backups of the objects and/or the database before you drop anything.Be One with the OptimizerTG |
|
|
|
|
Deleting Temporary Objects!