2

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.

2
  • Only the primary keys are missing? Have you checked Unique, Foreign Key and other (CHECK) constraints? Commented Jun 27, 2013 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
    Commented Jun 27, 2013 at 5:42

2 Answers 2

2
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
4
  • 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
    Commented Jun 27, 2013 at 6:33
  • 2
    @ProfK then why did you accept the answer? Commented Jun 27, 2013 at 13:56
  • @AaronBertrand I asked about primary keys, and Matt gave a satisfactory answer about primary keys. You figure it out.
    – ProfK
    Commented Jun 28, 2013 at 2:19
  • @ProfK sorry, I thought you were saying here and above that the answer didn't solve the problem. Commented Jun 28, 2013 at 2:41
-4

i did find this resource when looking for how to export keys.

https://www.mssqltips.com/sqlservertip/3443/script-all-primary-keys-unique-constraints-and-foreign-keys-in-a-sql-server-database-using-tsql/

2
  • Please don't add link-only answers.
    – Tom V
    Commented Mar 27, 2018 at 11:39
  • Well its too long to past the entire code. Commented Mar 27, 2018 at 14:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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