First off my name is Patrick and I just signed up for this forum. I've been working with SSMS for ~2 years as a BA. I've been tasked to go through a database ~200 tables, some have relationships others do not, (report tables and backup tables). ***Sorry this is SSMS not MYSQL (I suppose cleanup is clean up in theory though).
I recently went through and identified all objects using sys.object and other variation of these elements to put together a dictionary of the database. This includes FK, PK, Data Types, Ordinal numbers, max length, constraints, etc.
Next I reversed engineered the database with Visio 2010 and started to separate the tables into logical groupings. For instances all backup tables went into one ERD, temp tables went into another etc. (Not # ## temp tables, just tables with the naming convention of temp in them). I also separated some ETL tables in it's own ERD.
Here is where I stand. Any advice or an approach you would recommend?
This is my first time cleaning up a database and while I understand the basics, don't delete tables and what all the different constraints etc are. I just don't have the high level approach in place. Any strategy or suggestions would be greatly appreciated.
Last edited by VLOOKUP; 10-14-14 at 15:13.
Reason: Wrong RDBMS FORUM
Next step would be publish the list of tables you want to dump to whatever user base is appropriate, and if there is no squeak of protest, go ahead and rename the tables, and remove permissions on those tables from whatever groups have access to them (in case you have users smart enough to get around the re-naming). The rename of the tables should sufficiently break any views, functions or stored procedures that refer to these tables. Just withdrawing permissions won't work as well, due to the permission checks on chains of ownership.
Thanks for the follow up. Good call on renaming tables just in case. I am sure a few views will break but most of them are one time designs not high use objects. Thankfully I am not pulling the trigger so I submitted a list of tables that I felt could be renamed/removed. ~ 20 out of 200 not bad 10%.
I know there are other tables that can be removed ~35% of them are stand alone tables, some have reports some don't UGH
***Total noob question here. I'm assuming stand alone tables can be updated various ways, without relationships. I'm nervous they have these tables updating some other method than cardinality/relationships.
I am strangely quite fond of "scream testing" stuff, despite the fact it goes against most best practices.
As long as you are available to reverse the procedure in a matter of moments, then run with it!
SSMS has a lovely option on the right-click menu of an object labelled "View Dependencies" (I think it is probably just querying sys.sql_dependencies) that should help you identify what other objects have physical dependencies on the object in question (and vice-versa).
Another option is to query sys.all_sql_modules. Ferinstance:
WHERE definition LIKE '%dbo.temp_thing%'
Tables that have no "hard" relationships to other tables (in the form of foreign keys) can be updated however the user likes. If no relationships are defined among the tables, SQL Server happily concludes there is no reason to check if rows exist in one place, before adding some rows to another.
Thanks for following up. Lot's of good information here.
I renamed a few tables this AM, no one is screaming yet, however most of our jobs run in the pm so we shall see, I'll log on from home to review the output logs.
Since sys.object was brought up I was wondering if you had some thoughts on this dilemma. When I do a reverse engineer in Visio it shows the column descriptions via notes. These notes are not column notes but some type of metadata, which the DBA must of created when building the tables. Is there a system object I can join to, to join the notes to the columns, either through name or ordinal values?
Thanks again all!
*** Did some digging and found a sys.extended_properties table out there that has a major_id and minor_id. I believe the minor id is 0 for table attribute and then utilizes the column_id related to the column number. E.G. First column would be 1 and so on, if there is a note or extended property added to that particular column it will capture in that table. So you may see minor id 0, 4, 5 0 would be the table description, 4 and 5 are the 4th and 5th column descriptions. The rest have been omitted. (I think)
The major_id I believe can be joined on object_id which will match at the table level but won't align up at the column level. I tried to do a join with an and clause. Join major_id = t.object_id and minor_id = c.column_id Returned no results. I'm sure my logic is crap right now since I've been performing other task that require the other side of the brain.
Anyway I thought I would post this out there.
The tables I am using right now
I'll poke around a little more this is interesting
I found this on MS site
SELECT major_id, minor_id, t.name AS [Table Name], c.name AS [Column Name], value AS [Extended Property]
FROM sys.extended_properties AS ep
INNER JOIN sys.tables AS t ON ep.major_id = t.object_id
INNER JOIN sys.columns AS c ON ep.major_id = c.object_id AND ep.minor_id = c.column_id
WHERE class = 1
ORDER BY [Table Name];