Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have just discovered that deployed copies of one of our databases have no primary keys. This is probably the fault of the SSMS Import/Export wizard, which has done the same to me. Our master copy of the schema still has primary keys, and I would like to generate a script that will add those keys to any deployed databases without them.

The best I can hope for is to just do something to script all primary keys, but I will be happy to also just extract the names of the columns that make up these keys and generate my own script.

share|improve this question
Only the primary keys are missing? Have you checked Unique, Foreign Key and other (CHECK) constraints? – ypercubeᵀᴹ Jun 27 '13 at 5:30
@ypercube I was about to add that other indexes are missing. I haven't seen anything else missing, but I'm on table 30 or so of 653 tables. I am certain this is the work of the SSMS Export/Import wizard. – ProfK Jun 27 '13 at 5:42
up vote 2 down vote accepted
 (SELECT + '.' as colName,, idx.is_unique, idx.is_primary_key, idx.is_unique_constraint
    sys.indexes idx INNER JOIN
    sys.objects obj ON idx.object_id = obj.object_id INNER JOIN
    sys.index_columns idxcol ON idxcol.index_id = idx.index_id AND idxcol.object_id = idx.object_id INNER JOIN
    sys.columns col ON col.column_id=idxcol.column_id AND col.object_id = idxcol.object_id
WHERE a.is_primary_key = 1
share|improve this answer
Thanks Matt. I found it's not just PK's, but all indexes, so if I leave there 'where clause' off, I have 533 missing indexes. – ProfK Jun 27 '13 at 6:33
@ProfK then why did you accept the answer? – Aaron Bertrand Jun 27 '13 at 13:56
@AaronBertrand I asked about primary keys, and Matt gave a satisfactory answer about primary keys. You figure it out. – ProfK Jun 28 '13 at 2:19
@ProfK sorry, I thought you were saying here and above that the answer didn't solve the problem. – Aaron Bertrand Jun 28 '13 at 2:41

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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