THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Adam Machanic

Adam Machanic, Boston-based SQL Server developer, shares his experiences with programming, monitoring, and performance tuning SQL Server. And the occasional battle with the query optimizer.

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

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
 
Published Sunday, April 04, 2010 11:48 PM by Adam Machanic

Attachment(s): script_table_keys.sql

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Patrick Cahill said:

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.

April 5, 2010 10:44 AM
 

Uri Dimant said:

Thanks Adam ,as usual, very usefull script

April 6, 2010 1:17 AM
 

Uri Dimant said:

Thanks Adam ,as usual, very usefull script

April 6, 2010 1:17 AM
 

Uri Dimant said:

Thanks Adam ,as usual, very usefull script

April 6, 2010 1:17 AM
 

Brian Tkatch said:

bookmarked, thanx adam!

April 7, 2010 10:51 AM
 

Ron Holyfield said:

Hope to not have to deal with this but bookmarked in case - Thx.

April 12, 2010 2:06 PM
 

Kimberly L. Tripp said:

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

April 22, 2010 6:26 PM
 

Adam Tappis said:

Great script, thanks. Hope you don't mind me mentioning but you have left one reference to INFORMATION_SCHEMA, on the first line :)

April 25, 2010 11:18 PM
 

LLL said:

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.

April 21, 2011 9:17 AM
 

Bigpa said:

Excellent, your script has really helped me out !

July 28, 2011 10:15 AM
 

Binyam said:

Many thanks Adam for all the scripts. You are making me lazy !!??

May 25, 2012 10:58 AM
 

gaol said:

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

July 30, 2012 6:16 AM
 

Andrew said:

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?

July 22, 2013 5:57 PM
 

Andrew said:

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.

July 22, 2013 6:15 PM
 

Adam Machanic said:

@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.

July 27, 2013 4:05 PM
 

Rene said:

@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) :)

July 31, 2013 3:14 AM
 

Adam Machanic said:

@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 :-)

July 31, 2013 9:42 AM
 

Rene said:

@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 :)

August 1, 2013 1:44 AM
 

donald said:

I have used this on SQL20008 / 2012 and it only works for 1 table, is that right ?

July 16, 2014 2:04 PM

Leave a Comment

(required) 
(required) 
Submit

About Adam Machanic

Adam Machanic is a Boston-based SQL Server developer, writer, and speaker. He focuses on large-scale data warehouse performance and development, and is author of the award-winning SQL Server monitoring stored procedure, sp_WhoIsActive. Adam has written for numerous web sites and magazines, including SQLblog, Simple Talk, Search SQL Server, SQL Server Professional, CoDe, and VSJ. He has also contributed to several books on SQL Server, including "SQL Server 2008 Internals" (Microsoft Press, 2009) and "Expert SQL Server 2005 Development" (Apress, 2007). Adam regularly speaks at conferences and training events on a variety of SQL Server topics. He is a Microsoft Most Valuable Professional (MVP) for SQL Server, a Microsoft Certified IT Professional (MCITP), and an alumnus of the INETA North American Speakers Bureau.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement