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
— Get all existing primary keys
DECLARE cPK CURSOR FOR
SELECT TABLE_NAME, CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE constraint_type=’PRIMARY KEY’
ORDER BY TABLE_NAME
DECLARE @PkTable SYSNAME
DECLARE @PkName SYSNAME
— Loop through all the primary keys
OPEN cPK
FETCH NEXT FROM cPK INTO @PkTable, @PkName
WHILE (@@FETCH_STATUS = 0)
BEGIN
DECLARE @PKSQL NVARCHAR(4000) SET @PKSQL = ”
SET @PKSQL = ‘ALTER TABLE ‘ + @PkTable + ‘ ADD CONSTRAINT ‘ + @PkName + ‘ PRIMARY KEY CLUSTERED (‘
— Get all columns for the current primary key
DECLARE cPKColumn CURSOR FOR
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_NAME = @PkTable AND CONSTRAINT_NAME = @PkName
ORDER BY ORDINAL_POSITION
OPEN cPKColumn
DECLARE @PkColumn SYSNAME
DECLARE @Order SYSNAME
DECLARE @Index varchar(max)
DECLARE @PkFirstColumn BIT SET @PkFirstColumn = 1
— Loop through all columns and append the sql statement
FETCH NEXT FROM cPKColumn INTO @PkColumn
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF (@PkFirstColumn = 1)
SET @PkFirstColumn = 0
ELSE
SET @PKSQL = @PKSQL + ‘, ‘
Select @Order=Case is_descending_key when 0 then ‘ ASC’ ELSE ‘ DESC ‘ END
from sys.index_columns ic join sys.columns c
on ic.index_column_id=c.column_id
and c.name=@PkColumn
SET @PKSQL = @PKSQL + @PkColumn + @Order
— print charindex(‘ASC,’,@pksql)
FETCH NEXT FROM cPKColumn INTO @PkColumn
END
Select @Index=’) WITH ‘+'(PAD_INDEX =’+CASE is_padded when 0 then ‘OFF’ ELSE ‘ON’ END+
”+’, STATISTICS_NORECOMPUTE =’+CASE no_recompute when 0 then ‘OFF’ ELSE ‘ON’ END+
”+’, SORT_IN_TEMPDB =’+CASE is_padded when 0 then ‘OFF’ ELSE ‘ON’ END+
”+’, IGNORE_DUP_KEY =’+CASE is_padded when 0 then ‘OFF’ ELSE ‘ON’ END+
”+’, ONLINE =’+CASE is_padded when 0 then ‘OFF’ ELSE ‘ON’ END+
”+’, ALLOW_ROW_LOCKS =’+CASE ALLOW_ROW_LOCKS when 0 then ‘OFF’ ELSE ‘ON’ END+
”+’, ALLOW_PAGE_LOCKS =’+CASE ALLOW_PAGE_LOCKS when 0 then ‘OFF)’ ELSE ‘ON)’ END
+’ ON ‘ +'[‘+fg.name+’]’
from sys.indexes i join sys.stats s
on i.name=s.name
join sys.filegroups fg
on fg.data_space_id=i.data_space_id
and i.name=@PkName
SET @PKSQL = @PKSQL+@index
CLOSE cPKColumn
DEALLOCATE cPKColumn
— Print the primary key statement
PRINT @PKSQL
FETCH NEXT FROM cPK INTO @PkTable, @PkName
END
CLOSE cPK
DEALLOCATE cPK
Pinal, please try the above script for scripting out primary keys at database level and let me know where you think it needs amendment.
Manu
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 John Paul Cook,
Excellent script and your blog is one very good read always.
Kind Regards,
Pinal
Hi Pinal,
I updated my script and added code to handle ascending/descending sort order and to incorporate a suggestion from a reader.
Sir,
I should go over it tomorrow and with due credit will publish your link in next week.
kind Regards,
Pinal
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