In a recent database migration project, I encountered a unique challenge. The SQL Server tables had been successfully moved from the old database to the new one, but the Primary Key and Foreign Key constraints were yet to be transferred. This posed a problem as SQL Server Management Studio (SSMS) does not provide an option to script all the keys at once. Manually scripting each key for a database with numerous tables can be a complex and time-consuming task.

After researching possible solutions, I came across a helpful article by Kuldip on SQL SERVER – Two Methods to Retrieve List of Primary Keys and Foreign Keys of Database. While the article provided insights into retrieving the names of Primary Key and Foreign Key constraints, it did not offer a script to generate the keys themselves.

Therefore, I am reaching out to the SQL Server community for assistance. If any of you have a T-SQL script that can generate Primary Key and Foreign Key scripts for all tables in a database, please share it with me. You can either post it in the comments section below or email it to me at pinal ‘at’ sqlauthority.com. I will make sure to give you due credit when I publish it on this blog.

Here is an example of the desired T-SQL script:

ALTER TABLE [Person].[Address] ADD CONSTRAINT [PK_Address_AddressID] PRIMARY KEY CLUSTERED ([AddressID] ASC) ON [PRIMARY]
GO

By collaborating and sharing our knowledge, we can help strengthen the SQL Server community and make database management tasks more efficient for everyone involved. I encourage you to spread the word and actively participate in finding a solution to this challenge.

Thank you in advance for your contributions!