| Author |
Topic |
|
A_Goss
Starting Member
4 Posts |
 Posted - 2003-04-30 : 10:42:09
|
| Due to space constraints on a development server (SQL Server 7.0) I am hoping to identify who created tables in several databases (ownership changed to dbo). Many times these tables are forgotten about once everything goes into production. I'd like to clean these up to recover some much needed space. TIA,Andy |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-04-30 : 12:17:44
|
| If the owner is dbo, then you aren't going to be able to figure out who created the tables. Why don't you just send out an e-mail to the developers to figure out which tables that they really need? One really shouldn't just drop tables if they don't know if they are in use or not.Tara |
 |
|
|
A_Goss
Starting Member
4 Posts |
Posted - 2003-04-30 : 13:27:48
|
| Thanks for the reply. The intent wasn't to just delete the tables. We have sent out an e-mail(s) but as you can expect not everyone responds.I was hoping to develop a tool which we can go back to user "X" and say, "You created table "ABC" which is taking up "Y" amount of space. Is this needed any longer? If not, can you either truncate the table or delete it?" It also allows us to identify tables that were created by individuals no longer with the company. We could then communicate with their peers and find out if the table is still needed.I felt that based on everything I have read it wasn't possible using a SQL command. That doesn't mean that someone else may not have another suggestion or idea. Speaking of another idea....Could the log files be used to determine something like this. Granted several facts would have to be considered like how far back the log files go back, etc., etc., etc....Thanks again,Andy |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-04-30 : 13:34:26
|
| Of course the log files could be used, but only if you have a 3rd-party product called Log Explorer from [url]http://www.lumigent.com/[/url].I have had the same problem as you in the past and the way that we have resolved it was to rename the table to X_DELETE_<tablename> so that their code blows up. Then they will contact you to let you know that they need it, so you just rename it back. Of course you should only do it to those tables that you have no knowledge.HTHTara |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-04-30 : 14:34:40
|
| Well I guess you can do that, but that doesn't address clean up (why btw do you allow developers to create tables on their own?)Anyway I would:Dump the databaseIf you can restore it to another boxMake that box a linked serverblow away the tables you don't needand wait for the phone callsset a procedure to copy tables back over with a sprocMOO(if at all possible)Brett8-) |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-04-30 : 14:39:26
|
quote:
(why btw do you allow developers to create tables on their own?)
We allow this on development only. We (two DBAs) can not keep up with development changes (rapid development occurs here), so we allow developers to make their own changes. We, of course, review the database changes when development changes are minimal and discuss any problem areas with the developers.Tara |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2003-04-30 : 14:52:52
|
| I prefer writing SQL scripts to build any table that we want to retain and cannot be blown away on a whim. Then those scripts get stored into Source Safe. Now we know 2 things: 1) Any table that doesn't have a CREATE script in Source Safe can be deleted. (I'd probably give the development team a day's notice to get their new scripts added before I wasted them) And 2) Any table that has a script in Source Safe has the name of who checked it in, so then we know who to talk to for those.We also have a test sandbox database where developers should go to "try something new" or "test a theory" and they know that anything in there is purely temporary, and they should be saving scripts to rebuild their test scenario in case the database gets purged.------------------------------------------------------The more you know, the more you know you don't know. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-04-30 : 14:56:49
|
| We put everything in Source Safe except tables. The scripts that we use to deploy the tables to other environments go into VSS, but not the individual table scripts. I plan on changing this at some point, but haven't gotten around to it yet (have to work with developers on this of course).Tara |
|
|
|
Andraax
Aged Yak Warrior
790 Posts |
Posted - 2003-04-30 : 16:46:26
|
| Too bad so many developers know so little about databases. The world would be a better place if they knew more. Then we'd no longer se things like:rs.open "select * from bla"while not rs.eof if rs.Fields("bla") = 17 then rs2.open "update bla set bla=18 where id = " & rs.Fields("id") rs.MoveNextwendrs.closeOne consultant actually did something just like this :( |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-04-30 : 16:51:32
|
quote:
Too bad so many developers know so little about databases. The world would be a better place if they knew more. Then we'd no longer se things like:rs.open "select * from bla"while not rs.eof if rs.Fields("bla") = 17 then rs2.open "update bla set bla=18 where id = " & rs.Fields("id") rs.MoveNextwendrs.closeOne consultant actually did something just like this :(
Oh my! I hope that you guys either got rid of him or taught him some things about T-SQL.Tara |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-04-30 : 17:06:02
|
Careful now .... I know at least 1 person who posts here regularly at SQL team giving advice to DBA's and helping them to normalize their data and optimize SELECT statements, who is technically a developer and NOT a DBA and quite proud of it ... (I won't give his or her identity away ) but I agree, developer or DBA or otherwise, it is frustrating when people abuse a great tool like SQL server and use it like Excel!- Jeff |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2003-04-30 : 18:27:54
|
quote:
but I agree, developer or DBA or otherwise, it is frustrating when people abuse a great tool like SQL server and use it like Excel!
That's funny Jeff, I often find myself in the opposite situation, being frustrated over people abusing Excel trying to make it act like a database. You know, Andraax, it's consultants like the one you described that give the rest of us a bad name. I hope you "disposed" of him appropriately.------------------------------------------------------The more you know, the more you know you don't know.Edited by - AjarnMark on 04/30/2003 18:28:39 |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-04-30 : 21:49:31
|
| LOL... excellent point, Mark! very true!!!- Jeff |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2003-05-01 : 03:01:26
|
quote:
who is technically a developer and NOT a DBA and quite proud of it
Moi ?  I saw some code very much like that at my last job Andraax. I nearly cried, I had to drop what I was doing and clean it up.It went something like this :(imagine the rest of the HTML stuff)Set results = conn.exec "select * from employment Where JobID=" & request.querystring("JobID")if not results.eof thenresponse.write results("JobName")set results2 = conn.exec "select * from from departmentswhere departmentid=" & request.querystring("DepartmentID")response.write results2("departmentName")results2.closeset results2 = nothingset results3 = conn.exec "select * from from location where locationid=" & request.querystring("locationid")response.write results3("LocationName")results3.closeset results3 = nothingend ifresults.close You get the point. The guy didn't know what a join was, so he passed the ID of every table in on the querystring and did a SELECT * for every one. Each one wasn't checked so the SQL Injection potential was huge. Too scary.I also used to get told to consult with the DBA about everything I did there, and life was difficult because the permissions kept getting messed with. It was all he seemed to do. Having your SQL code and database designs reviewed by someone that doesn't understand them is kinda comical. Then one day he told me I should signup to the SQL Team newsletter because they have some good articles I could learn from............... umm yeah DamianEdited by - merkin on 05/01/2003 03:08:31 |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2003-05-01 : 19:10:32
|
quote:
Then one day he told me I should signup to the SQL Team newsletter because they have some good articles I could learn from............... umm yeah
HA HA HA HA HA HA HA HA!.So then you said to him, "Hey this Merkin guy has written several articles and seems to really know what he's talking about. And he even lives near here. Maybe we should hire him to be the senior DBA and take all that heavy burden off of you."------------------------------------------------------The more you know, the more you know you don't know. |
|
|
|
|
A way to see who created a table??