Take the 2-minute tour ×
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. It's 100% free, no registration required.

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
add comment

1 Answer

up vote 2 down vote accepted
SELECT * 
FROM
 (SELECT obj.name + '.' +col.name as colName, idx.name, idx.is_unique, idx.is_primary_key, idx.is_unique_constraint
  FROM 
    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 obj.type_desc NOT IN ('SYSTEM_TABLE', 'INTERNAL_TABLE')) a
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
2  
@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
add comment

Your Answer

 
discard

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.