Updating columns in all tables

asked 2021-11-04T13:08:40.523+00:00
-- -- 666 Reputation points


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?



{count} votes

Accepted answer
  1. answered 2021-11-05T22:35:08.333+00:00
    Erland Sommarskog 67,721 Reputation points Microsoft MVP

    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) + ')
    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
    No comments

1 additional answer

Sort by: Most helpful
  1. answered 2021-11-05T06:48:19.4+00:00
    EchoLiu-MSFT 14,416 Reputation points

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

    No comments