12

I have to maintain and extend an old legacy system which contains webservice methods and database tables that are no longer used. Since I’m not entirely sure that the tables are really redundant, I’m afraid to drop them.

Is there any other way to achieve the same effect (tables cannot be used any more) without dropping them? My idea was to transfer them to a different schema (e.g., Deleted) from the current default, dbo.

IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'Deleted')
BEGIN
   EXEC('CREATE SCHEMA Deleted')
END

ALTER SCHEMA Deleted TRANSFER dbo.TableName;

Is there any other option or are there any drawbacks to the schema approach?

| improve this question | |
7

Is there any other way to achieve the same(tables cannot be used anymore) without dropping them?

A schema change is a very fast operation - just metadata change is required. The original idea I got was from Aaron Bertrand's blog - Schema Switch-A-Roo.

You can follow the steps from my answer here

Obviously there are other methods like sp_rename N'old table', N'new table' or just deny permissions to the table.

| improve this answer | |
  • I wasn't sure what answer i should accept because all are helpful. I didnt know Aaron's article, so i've accepted this since it contains more informations (even if only linked). – Tim Schmelter May 18 '16 at 9:47
  • @TimSchmelter Glad you found it useful. It does not make sense to repeat here what the article or my answer (linked). Thats why I have referenced it. – Kin Shah May 18 '16 at 13:38
12

A couple of other options are to just rename the tables, or if they have clustered indexes, you can disable the clustered index.

| improve this answer | |
  • Thanks. I didn't know that disabling a clustered index makes the table unusable. What are the pros and cons of these approaches (+ schema)? – Tim Schmelter May 17 '16 at 9:07
  • 5
    @TimSchmelter a con of disabling the CI is that to enable it again you need to rebuild the index. One other option is deny permissions on the table to the public role though database owner or sys admins will still see them and also possibly through ownership chaining. – Martin Smith May 17 '16 at 10:12
  • Prior to dropping a table, or dropping a column from a table, I often rename it by adding "_deprecated" or some such to the end of the name. Then if there are no errors, there must not be anything referencing it. – Jay May 17 '16 at 14:01
  • Permissions changes are chancy. If the service account running an application is the dbo, or even worse sysadmin then it will completely ignore any type of DENY. Hopefully they aren't, but it does happen. – Kenneth Fisher May 17 '16 at 18:25
6

Remove the permissions on the table from the Role(s)/ Group(s)/ Account(s) that [might] be using it.

If anything blows up, put them back [quickly].

Hint: Using a script to do make these changes would be a really, really Good Idea.

| improve this answer | |
  • As would testing on a non-production database. ;) Hopefully, that was obvious to the OP, though. – jpmc26 May 17 '16 at 23:31
  • @jpmc26. i will test in a non-production database first. But the problem is that i want to know if functions or database objects are used from outside(not only via webservice methods but also directly on the database or admin tools in other locations of the company). – Tim Schmelter May 18 '16 at 9:36
  • Hm. If you're looking for direct DB access by DBAs, it sounds like logging is in order. It might not be very likely that you'd see the same usage in prod and non-prod from manual work. I'm not really sure how easy it would be to log these operations, but if you had a log in prod, you could analyze it to look for usages. – jpmc26 May 18 '16 at 9:40
  • @jpmc26: it's ok if those admins will fall on their face, they'll report it. Not ok with customers, but that can be checked on the test system before rollout. – Tim Schmelter May 18 '16 at 9:42
3

Removing permissions is not generally going to work because you can't be CERTAIN that someone doesn't have permissions. Possibly through a group, role or even because they are sysadmin (although let's hope not).

For tables you can disable them. And that is a quick process. However to enable them requires you to rebuild them and for a large table that could take you quite a while.

Your best bet is going to be to move the object into a new schema (as you suggested) or re-name the object. Both of these operations are quick and easy both to do and to undo. Permissions will also remain in place in both directions.

An additional step you can take is to add a "TBD note" in the extended properties of the object. You can make a note of when you made the change, and/or any notes you might have on why you feel it is safe to get rid of.

All that said I would run an extended events session (or profiler trace) for a few days to be sure you have all of the objects being used. You can heavily limit the session to just the object name & when it was touched to reduce the overhead. Also make sure you run this session for a few days on either side of the end of month and possibly even end of quarter to be sure you have everything.

| improve this answer | |
3

Remove the permissions as Phil W. suggests.

Also remove the permissions from any stored procedures that use the tables. In SQL Server, (I don't know about others) permissions are chained from a calling object (e.g. the stored procedure) to the called object (e.g. a table).

| improve this answer | |
  • The task was to let all tools, functions, objects, queries (even in remote databases) fail that try to access these tables. If i have to modify a stored-procedure i already have to know that it uses it. Or is there any simple way to determine that a table is used by a stored-procedure? – Tim Schmelter May 17 '16 at 14:37
  • I did not suggest modifying the stored procedure, only removing its EXECUTE permission. As you say, its easy to restore the permission if necessary. You can see what tables are used by a stored procedure: In SQL Server Management Studio, Object Explorer, select your database -> Programmability -> Stored Procedures. Right-click on an sproc name and choose View Dependencies. Select Objects on which [sproc name] depends. – Peter Bill May 17 '16 at 15:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.