Updating columns in all tables

Question

Hi

How can I go through all tables of two identical databases and where in the first db, column of a table is a primary key and/or identity column, the relevant column in the corresponding table in the second database is also set accordingly?

Thanks

Regards

Answer 1

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

Answer 2

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.

Regards,
Echo

---

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".