I am a programmer changing the way a process works, and will be using a different table to do this. The process is just sending emails from a web application. Currently the database scripts run during a batch each night and create entries in a table that contain the subject, and the entire body of the email. I am moving to a process where only the variable elements will be held in the table.

I am just wondering what the accepted, or most common strategy is for doing this. I've spoken to a few of our database people and they suggested to create an entirely new table and use this going forward.

I am fine with doing it this way, and was wondering if it's a good idea to alter the current table's name, to something like EmailTableOld and create my new table with the original name EmailTable so I don't have to create additional permission scripts and set up new synonyms while removing the old ones.

Is this a good idea? I have identified everything that refers to the table and there are only a few things mostly security granting scripts, and the scripts that actually create email content. The table does not reference or is referenced by any other tables currently. The entire contents of the old table must be kept.

Any suggestions etc are welcome

1 Answer 1


sp_rename or swapping schemas are the typical ways to accomplish this. Neither really saves you anything about permissions scripts because whether you renamed the new object or swapped it into the dbo schema, it still needs to have permissions applied to match the old version of the table.

For a long time I've preferred swapping schemas simply because I used to do this in an automated job and the "Caution: renaming objects blah blah" warning messages were filling the SQL Server Agent logs. That message is not suppressible but the ALTER SCHEMA...TRANSFER statement does not raise such a message. I have asked for a way to suppress this warning but it was rejected.

(Of course another avenue to consider is to archive the current data and alter the current table to meet the new requirements - but I don't know if that's something you haven't considered, or if it has and there's a reason you can't do that.)

  • For every build we do we drop all permissions and apply them again, so if the name is the same it should apply all the needed permissions. I'm not sure what you mean be swapping schemas? The table isn't related to any others but there are still quite a few othe's in the schema.
    – dann.dev
    Jun 5, 2012 at 3:10
  • @dann.dev I have spoken about this technique before... have a look at this question for example... Jun 5, 2012 at 3:12
  • Interesting, and very clever, but I think it's probably overkill for what I want, I might stick with renaming the table or just creating a new one with a different name, thanks for the help.
    – dann.dev
    Jun 5, 2012 at 3:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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