Here is a script to generate all primary keys in a database that you can run in the other. Copy and paste the generated result to a query window. If there already is a primary key for a table, you will get an error on that table.
If you also want to copy the IDENTITY property, that is a lot more complex, since there is no straightforward way of adding that property to the column with ALTER TABLE.
SELECT 'ALTER TABLE ' + quotename(s.name) + '.' + quotename(o.name) + ' ADD CONSTRAINT ' + quotename(kc.name) + ' PRIMARY KEY (' + substring(ic.list, 1, len(ic.list) - 1) + ') go ' FROM sys.schemas s JOIN sys.objects o ON s.schema_id = o.schema_id JOIN sys.key_constraints kc ON kc.parent_object_id = o.object_id CROSS APPLY (SELECT quotename(c.name) + ', ' FROM sys.index_columns ic JOIN sys.columns c ON ic.object_id = c.object_id AND ic.index_column_id = c.column_id WHERE ic.object_id = o.object_id AND ic.index_id = kc.unique_index_id ORDER BY ic.key_ordinal FOR XML PATH('')) AS ic(list) ORDER BY s.name, o.name
Hi @-- -- ,
The following code can help you find the primary keys of all tables in a database:
select t.name tablename,c.name columnname, si.index_column_id number from sys.index_columns si join sys.columns c on si.object_id = c.object_id and si.column_id = c.column_id join sys.indexes i on si.object_id = i.object_id and si.index_id = i.index_id join sys.tables t on i.object_id = t.object_id join sys.schemas s on t.schema_id = s.schema_id where i.is_primary_key= 1
If there are not many tables in your database, it is recommended that you manually match the columns of the new corresponding table.
The following example creates a primary key on the TransactionID column in the AdventureWorks database:
ALTER TABLE Production.TransactionHistoryArchive ADD CONSTRAINT PK_TransactionHistoryArchive_TransactionID PRIMARY KEY CLUSTERED (TransactionID);
For more details, please refer to:
Create Primary Keys
If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.
If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".