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

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

    465
    23

    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

     

    File Attachment: script_table_keys.zip

    Previous articleT-SQL Tuesday #004: IO, IO, It’s Off to Disk We Go!
    Next articleT-SQL Tuesday #005: Reporting
    Adam Machanic helps companies get the most out of their SQL Server databases. He creates solid architectural foundations for high performance databases and is author of the award-winning SQL Server monitoring stored procedure, sp_WhoIsActive. Adam has contributed to numerous books on SQL Server development. A long-time Microsoft MVP for SQL Server, he speaks and trains at IT conferences across North America and Europe.

    23 COMMENTS

    1. Thank you for the script Adam. I don’t know how many times I have had to go find all the FKs so I could make a change to a column. They will save me a lot a pain in some development projects that we are working on.

    2. Hey there Adam (et al) – If you’re interested in the code that I use to generate the following:
      * disabling the NC indexes
      * disabling the FKs
      This is where you’d change the CL index…
      * re-enabling (and very important RECHECKING! of the FK constraints)
      * REBUILDS of the disabled nonclustered indexes.
      All of this is described and detailed in the SQL Server Magazine Q&A where I answer how to Change the Definition of a Clustered Index. Here’s the link: http://www.sqlmag.com/blogs/sql-server-questions-answered/sql-server-questions-answered/tabid/1977/entryid/12749/Default.aspx.
      It’s not a script (like yours) but more the process and TSQL to change a clustered index.
      Cheers!
      kt

    3. Great script, thanks. Hope you don’t mind me mentioning but you have left one reference to INFORMATION_SCHEMA, on the first line 🙂

    4. A MILLION THANKS!  This script is perfect for what I need.  I want to be able to truncate the tables in a schema, pull a copy of data in, and then re-apply the constraints.  I added an @FKOnly parameter to determine if foreign keys only are needed in output.  Also added @DropOrCreate parameter to get drops all at once and creates separately as I’m looping a call to this SP for every object.

    5. why when i run the script error near in ‘kc.key_columns’? any body can give me the example? because i don’t know how to replace like [– Create Candidate Keys] in table

    6. it works fine for the primary key & referencing foreign keys, but seems to be ignoring the other indexes on the table. is that intended? it seems to include the ability to create ‘drop index’ and ‘create index’ statements, but I don’t get them in the output as I was expecting. Am I missing something?

    7. Answering my own question: yes, I was missing something: it "ignored" the other indexes because they were not unique (i.e., this part of the query in your script didn’t match: "AND i.is_unique = 1").
      Thank you very much for posting this script. Do you know if it’s necessary to drop & then recreate an index or key if you want to change the collation of character-type column[s] used in the key/index? Might you get bogus query results, e.g., if you don’t take that step, but just change the collation of the column? That’s the task that led me to find your script, fwiw: needing to change the collation of character-type columns throughout a database with many (~200) such columns.

    8. @Andrew: It ignores nonunique indexes since it’s only designed for "candidate keys" — i.e. keys other tables might reference. I use it when I need to modify the keys on tables that are heavily referenced by other tables.
      I believe a collation change does require a drop of the index.

    9. @Adam: Do you have any plans on making this nice script work on a SQL Server 2012 in the near future?
      I’m trying to clean up all our databases, so they will all use the same collation and compatibility level (2012) 🙂

    10. @Rene: It actually works fine on 2012, if you comment out the check. I put that there in case there were some new features I’d have to implement script changes for. But I’m not aware of any at this point, except perhaps nonclustered Columnstore indexes. (And I’m not sure that fits into the use case for this script.) We’ll call it a known gap 🙂

    11. @Adam: Thank you for your answer, I actually gave it a go yesterday and you were right… it worked perfect 🙂
      Now I’m just waiting at hoping for a script that will convert regular indexes into columstore indexes 😉
      Thanks again for your answer 🙂

    12. Hi Adam, thanks for the script. I think there’s a little bug in it. I have a Foreign Key with "ON DELETE CASCADE" that your script doesn’t seem to generate.
      Thanks.

    13. @donald
      Yes, one table at a time.
      @serge
      Thanks for the catch. Looks like you can grab that information from the delete_referential_action_desc column in sys.foreign_keys. I’ll leave that as an exercise for you, as I don’t like cascading constraints 🙂
      Apologies to you both for not replying sooner!
      –Adam

    14. @Adam Thanks for your script, it is an amazing help for us in our endeavour to change the collation for all tables in several existing databases.

      As we have some indexes that are indeed sorted with DESC order, I have adjusted your script to honor this property.

      Line 174 now reads
      ”’,” + @crlf + ” ” + QUOTENAME(c.name) + CASE ic.is_descending_key WHEN 1 THEN ” DESC” ELSE ” ASC” END AS [text()] ‘ +

      Hope this small contribution helps someone 🙂

      • Thank you for sharing this enhancement. I’m embarrassed to have not included it in the original script myself! I very rarely see descending indexes — but it’s a major oversight.

    Comments are closed.