Hello all,
I have searched high and low and can't locate a procedure to remove system tables. I have read through [UP_POPULATE_REFERENCE_METADATA] and it doesn't seem to remove the table and any references.
There is nothing in the help documentation about deleting system tables. If anyone has removed/inactivated local/system tables and know how to do this or can point me in the direction of instructions I would be much obliged.
Thank you,-Lisa
Lisa,
Is there a reason the table needs to be DELETED? (Also, I am assuming you are talking about local system tables and not standard ones which definitely should be just left alone.)
I mean, worst case scenario, should it not be just as simple as removing access to that system table from all user groups?
I am sure they CAN be deleted, you would probably need to delete from both the reference tables and all user group and control group references first. But I am not sure why you would need to actually delete them, unless you are just trying to clean up everything to the ideal. I suppose I would definitely encourage testing first if that is your goal.
John
They are definitely local system tables and they were part of a decommissioned project and need to be removed. I was hoping to avoid removing tables manually as i'm worried that might break something and can't find any utilities to remove them (the same way [UP_POPULATE_REFERENCE_METADATA] added them.
Unless there is a really serious reason for deleting them, then you should not need to worry about that. Just go into the Security module and delete all user groups from their access to those tables. Then no one will see them anymore, and, excepting those who have direct access to SSMS, for all intents and purposes, they will have been "deleted" for all other users. And those who have access to SSMS can simply ignore them.
Frankly, though, if they are local tables, unless you have some procedures (scheduled or called by utilities/reports), you are probably good to just drop those tables. But I often leave those alone for at least a while in case we want to see what we had been considering.
Once I am certain that there is ZERO reason for their existence either for proper functionality of anything or even historical knowledge, I HAVE been known to delete such tables, but unless your database and these local tables themselves are huge, leaving them there should not affect anything.
Thank you. I already removed security access. The bigger issue is that it's part of a large project that was/needs to be removed and I wanted to find an official/clean way to remove all pieces without having to drop the table.
What else is involved in the larger project? Custom screens, reports, etc...?
When we have had failed initiatives, for our organization, removing all traces from the End User Tessitura experience has been basically all that was needed, no need to delete or drop the tables directly from the database.
Or are parts of it still going to be used but not other parts? I confess myself to be quite curious now. I am more than happy to chat offline. My phone number is on my profile, and my e-mail address is JMoskal@TheCenterPresents.org.
I have a stored procedure that I use to try and figure out whether or not a SQL object has dependencies. It's old, but modestly thorough (looks through stored procedure text, triggers, etc.) and _should_ even find foreign key relationships.
That said, I believe there are better utilities out there that I've seen mentioned in the past, but I can share mine if you are interested.
I'm routinely told to just ignore deprecated SQL objects, but I understand the frustration: 1) you can remove system tables from regular user's view, but you can't remove them from an administrator's system tables tab and 2) they interfere with maintenance and troubleshooting by providing more code that you have to comb through. The latter happens to me all the time.
I've always found SSMS's Object Explorer -> Right Click -> View Dependencies to be quite thorough. I believe I've seen blogs suggesting that it isn't just calling an API endpoint or system procedure in the background, so there is likely quite a bit of code in SSMS itself that goes into sorting out the dependencies.
I haven't found it to be reliable, but I haven't tried it in a long time. I think the issue I had with it in the past is that it only links dependencies if the objects are created in the right order (i.e. dependent objects are created after their dependencies), but if they are built out in a script that has the objects sorted in some other order, or if various objects are dropped and re-added at a later date, then the dependency network is broken.