Rejuvenated: Script Creates and Drops for Candidate Keys and Referencing Foreign Keys

Adam Machanic — Mon, 05 Apr 2010 02:48:00 GMT

Once upon a time it was 2004, and I wrote what I have to say was a pretty cool little script. (Yes, I know the post is dated 2006, but that's because I dropped the ball and failed to back-date the posts when I moved them over here from my prior blog space.)

The impetus for creating this script was (and is) simple: Changing keys can be a painful experience. Sometimes you want to make a clustered key nonclustered, or a nonclustered key clustered. Or maybe you want to add a column to the key. Or remove a column. Or change a data type. Whatever you want to do, you need to remember to drop all of the referencing keys, drop the key you want to change, then put everything back the way it was.

... well, that's not quite true. I was talking with Kimberly Tripp this week and she happened to mention that she was working on a script for the same scenario I was once concerned with. Her script is based on the idea that you don't have to drop the constraints--you can just disable them. I thought back to my own script, and decided to give it a much-needed major update. But I decided that if Kimberly is working on a version based on disabling constraints, I'll leave mine as-is. Then you can use either script--or both--depending on your scenario. If you're just changing a clustering key, Kimberly's may be easier to work with. If you're moving columns in or out of a key, my script will probably get you there a bit faster. More tools means better options! Watch her blog for her version, which should be released shortly.

So what have I updated in my own script?

First of all, the new version is written entirely against the SQL Server 2005+ catalog views. I tried writing the original against INFORMATION_SCHEMA with some goal of cross-compatibility in mind and failed miserably in that pursuit, even then. I've since given up hope of ever writing code that will run (properly) on multiple DBMSs, so it's catalog views all the way from here on out.
The old version only scripts those indexes that are actually declared as "constraints". But a unique index can act as a candidate key just as well as a unique constraint. And with the addition of SQL Server 2005's "included columns" feature, there are lots of great reasons to use a unique index instead of a constraint. So those are now scripted as well.
The old version didn't properly handle system-named constraints. The new version tries to keep things consistent, scripting an explicit name only when one was specified at create time.
The old version didn't handle the various index options such as fill factor, padding, etc, that were around in SQL Server 2000, let alone the newer features added in SQL Server 2005 and 2008. The new version handles all of them: fill factor, padding, ignore duplicate keys, allow row or page locks, and data compression are all supported.
Partitioning? Check--in the new version.
Disabled constraints? The old version may have scripted them in some cases. The new version ignores them, and rightfully so.
The old version didn't properly quote the table names. Oops! Fixed.

There are a few areas that I'm still not touching: XML and FTS indexes, as well as other less-used features that can make the script not work quite as intended. In most cases should you be using these features the drops will simply fail, so you won't wind up with too much of a mess on your hands. But as always, test carefully before using this script. I'll happily fix any bugs you report, but I provide this with absolutely no warranty or guarantee.

To use the script, simply put SSMS into "results in text" mode, specify your target table, and hit F5, CTRL-E, or the Play button. The result should be a nicely-formatted script containing all of the drops and creates, in the correct order. (Foreign keys dropped first, then nonclustered indexes, then the clustered index, if appropriate. Creates are scripted in the reverse order.)

The script is attached to the post. Download, take a look, and let me know if you encounter any issues. Or if you wish to lavish me with praise.

Enjoy!

Note: Don't forget to configure the maximum text size in SSMS before using. The default is 256 characters--not enough for many cases. To configure, use the following sequence:

Tools->Options->Query Results->Results to Text->Maximum number of characters->8192

Search results matching tags 'Scripts' and 'indexes'

Rejuvenated: Script Creates and Drops for Candidate Keys and Referencing Foreign Keys