In one of my recent projects, a large database migration project, I confronted a peculiar situation. SQL Server tables were already moved from Database_Old to Database_New. However, all the Primary Key and Foreign Keys were yet to be moved from the old server to the new server.
Please note that this puzzle is to be solved for SQL Server 2005 or SQL Server 2008. As noted by Kuldip it is possible to do this in SQL Server 2000.
In SQL Server Management Studio (SSMS), there is no option to script all the keys. If one is required to script keys they will have to manually script each key one at a time. If database has many tables, generating one key at a time can be a very intricate task. I want to throw a question to all of you if any of you have script for the same purpose.
As per my opinion, I think the challenge is to get orders of the column included in Primary Key as well on the filegroup they exist.
Please note here that I am not looking for names of Primary Key or Foreign Key of database. I have already written an article for the same here : SQL SERVER – Two Methods to Retrieve List of Primary Keys and Foreign Keys of Database . I am looking for T-SQL script that generates Primary Key from the existing table for all tables in database. There are already a couple of answers on my post here from two SQL Experts; you can refer to those for example here.
Following is an example of T-SQL that we need to generate. I am looking for script that will generate T-SQL script for all the Primary Key and Foreign Key for the entire database.
ALTERÂ TABLE [Person].[Address] ADDÂ Â CONSTRAINT [PK_Address_AddressID] PRIMARYÂ KEYÂ CLUSTERED
(
[AddressID] ASC
) ON [PRIMARY]
GO
If you have a solution for the same, please post here or email me at pinal ‘at’ sqlauthority.com and I will post on this blog with due credit. Again, please spread the word and help community become stronger by your active participation.
Reference : Pinal Dave (https://blog.sqlauthority.com)
34 Comments. Leave new
Dear Sir,
I hv a pleasure to mail to you.
Still I’m not satisfied.
Shekhar Sinha, Guwahati, Assam , India
Hi Pinal,
I needed to script primary key creation today at work, so I wrote this script:
Comparing it to the submissions on your site, I see that I didn’t consider a descending primary key – we don’t have those in the applications I work on.
You have some good submissions, although most people overlooked using brackets in case the object name has embedded spaces. Also, nobody considered that a primary key could be nonclustered. That’s yet another reason to use the sys metadata views instead of INFORMATION_SCHEMA. It is an anachronistic myth that INFORMATION_SCHEMA views are better or more proper. All too often INFORMATION_SCHEMA views don’t provide enough detail to finish the job.
Hi Pinal,
I updated my script and added code to handle ascending/descending sort order and to incorporate a suggestion from a reader.
Pinal,
I wrote a script to create all foreign keys:
very good information on sql server
so…..is there a reliable script to create all primary keys?
I clicked on John Paul’s link to the script and get a display page error.
Oh , and per the first few responses to this. you cannot, as far as I can see, generate creation scripts for primary keys from management studio. Maybe I am missing something, but you should be able to.
Hi Please help me in below problem.
I need to deletesome the rows of all the child tables of a parent table based on some condition.
eg: Parent table A (havig A.ID is a primary key )
A.Id is the foreign/primarykey/ for many other tables. i.e many tables depend on the A – table first we need to delete all the rows of child tables of child tables …..
then we need to delete the rows in parent based on the condition (say A.StatusId=1) without using ondeletecascade. Please help me in this