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.

  • 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
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
  • 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
-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/

  • Please don't add link-only answers. – Tom V - Team Monica Mar 27 '18 at 11:39
  • Well its too long to past the entire code. – Jeffrey Holmes Mar 27 '18 at 14:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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