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
Nice Article Sir.
we can create the script for all the primary key and foreign keys for whole database.
Right click on databse and
use Tasks ->Generate Scripts and in this wizard you specify true for primary keys in choose script option.
like that we can create the script only for primary keys.
I use this option for creating scripts for primary keys.
should we reinvent the wheel…
Sir it’s not work for alter script but for create script it will work.
So i will try to create script for this and after i will give you answer for this question.
i check in sqlserver2000 it works fine, by using the generate script option we can create the script for alter for all the primary key in sqlserver 2000.
Script Generated by SQL2005
ALTER TABLE [table] ADD CONSTRAINT [PK_table] PRIMARY KEY CLUSTERED
(
[table_id] ASC
)WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 95, ONLINE = OFF) ON [PRIMARY]
——–
PAD_INDEX ?
SORT_IN_TEMPDB ?
IGNORE_DUP_KEY ?
FILLFACTOR ?
ONLINE ?
Another big difficulty at the time of copying a database to another location are the fields with “IDENTITY INCREMENT”
— Get all existing primary keys
DECLARE cPK CURSOR FOR
SELECT DISTINCT C.TABLE_NAME, C.CONSTRAINT_NAME , F.NAME
FROM SYS.INDEXES I
INNER JOIN SYS.FILEGROUPS F
ON I.DATA_SPACE_ID = F.DATA_SPACE_ID
INNER JOIN SYS.ALL_OBJECTS O
ON I.[OBJECT_ID] = O.[OBJECT_ID]
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C
ON O.NAME = C.TABLE_NAME
WHERE C.CONSTRAINT_TYPE = ‘PRIMARY KEY’
ORDER BY C.TABLE_NAME
DECLARE @PkTable SYSNAME
DECLARE @PkName SYSNAME
DECLARE @FileName SYSNAME
— Loop through all the primary keys
OPEN cPK
FETCH NEXT FROM cPK INTO @PkTable, @PkName , @FileName
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 @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 + ‘, ‘
SET @PKSQL = @PKSQL + @PkColumn
FETCH NEXT FROM cPKColumn INTO @PkColumn
END
CLOSE cPKColumn
DEALLOCATE cPKColumn
SET @PKSQL = @PKSQL + ‘)’ + ‘ ON ‘+@FileName
— Print the primary key statement
PRINT @PKSQL
FETCH NEXT FROM cPK INTO @PkTable, @PkName, @FileName
END
CLOSE cPK
DEALLOCATE cPK
— Used to generate all foreign keys for each Database
Create table #Scripts(scripts varchar(4000))
insert into #scripts
EXEC sp_MSforeachdb
‘USE ?
IF DB_ID(”?”) > 4 — Skip system databases
BEGIN
EXEC (”
SELECT ””ALTER TABLE ””+OBJECT_NAME(f.parent_object_id)+
”” ADD CONSTRAINT”” + f.name + ”” FOREIGN KEY””+””(””+COL_NAME(fc.parent_object_id,fc.parent_column_id)+””)””
+””REFRENCES ””+OBJECT_NAME (f.referenced_object_id)+””(””+COL_NAME(fc.referenced_object_id,
fc.referenced_column_id)+””)”” as Scripts
FROM .sys.foreign_keys AS f
INNER JOIN .sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id
”)
END’
select * from #Scripts
–Try setting you visual studio as Result to text.
declare @NewLinechar char(2)
set @NewLinechar = char(13) + char(10) — This one came from one of your articles
select ‘ALTER TABLE [‘ + s.name + ‘].[‘ + t.name + ‘] ADD CONSTRAINT [‘ + c.name + ‘]’ + @NewLinechar
+ ‘PRIMARY KEY ‘ + i.type_desc collate SQL_Latin1_General_CP1_CI_AS + @NewLinechar
+ ‘([‘ + col.name +’] ASC’ + @NewLinechar
+ ‘) on [‘ + ds.name + ‘]’ + @NewLinechar
+’GO’
from sys.key_constraints c
join sys.tables t
on c.parent_object_id = t.object_Id
join sys.schemas s
on t.schema_id = s.schema_id
join sys.indexes i
on c.unique_index_id = i.index_id
join sys.index_columns ic
on i.object_id = ic.object_id
and i.index_id = ic.index_id
join sys.columns col
on t.object_id = col.object_id
and ic.column_id = col.column_id
join sys.data_spaces ds
on i.data_space_id = ds.data_space_id
where c.type = ‘PK’
I think there is one more way by looping all the bases for foreign cases using while loop…
here it is
create table #Dbs(name varchar(100),isscaned bit)
insert into #Dbs
select name, 0 from sys.databases where database_id > 4 and state = 0;
–select * from #DBS
DECLARE @dbname varchar(100)
DECLARE @SQLSTR varchar(8000)
WHILE (SELECT COUNT(*) FROM #Dbs WHERE isscaned = 0) > 0
BEGIN
SELECT TOP 1 @dbname = name
FROM #Dbs
WHERE isscaned = 0;
set @SQLSTR = ‘USE ‘+ @dbname;
exec (@SQLSTR)
begin
select ‘ALTER TABLE ‘+OBJECT_NAME(f.parent_object_id)+ ‘ ADD CONSTRAINT’ + f.name + ‘ FOREIGN KEY’+'(‘+COL_NAME(fc.parent_object_id,fc.parent_column_id)+’)’+’REFRENCES ‘+OBJECT_NAME (f.referenced_object_id)+'(‘+COL_NAME(fc.referenced_object_id,fc.referenced_column_id)+’)’ as Scripts
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id
end
UPDATE #Dbs
SET isscaned = 1
WHERE name = @dbname;
END
I have come up with the following query to generate the script for all the foreign keys in the database:
SELECT ‘ALTER TABLE dbo.’ + T.NAME + ‘ ADD CONSTRAINT ‘ + FK.NAME
+ ‘ FOREIGN KEY (‘ + C.NAME + ‘) ‘ + ‘ REFERENCES dbo.’
+ RT.NAME + ‘( ‘ + RTC.NAME + ‘)’
+ CASE(FK.delete_referential_action)
WHEN 0 THEN ‘ON DELETE NO ACTION’
WHEN 1 THEN ‘CASCADE’
END
+ CASE(FK. update_referential_action)
WHEN 0 THEN ‘ON UPDATE NO ACTION’
WHEN 1 THEN ‘CASCADE’
END
FROM SYS.FOREIGN_KEYS FK
INNER JOIN SYS.OBJECTS O ON O.OBJECT_ID = FK.OBJECT_ID
INNER JOIN SYS.FOREIGN_KEY_COLUMNS FKC ON FK.OBJECT_ID = FKC.CONSTRAINT_OBJECT_ID
INNER JOIN SYS.TABLES T ON FK.PARENT_OBJECT_ID = T.OBJECT_ID
INNER JOIN SYS.TABLES RT ON FK.REFERENCED_OBJECT_ID = RT.OBJECT_ID
INNER JOIN SYS.COLUMNS C ON T.OBJECT_ID = C.OBJECT_ID AND C.COLUMN_ID = FKC.CONSTRAINT_COLUMN_ID
INNER JOIN SYS.COLUMNS RTC ON RT.OBJECT_ID = RTC.OBJECT_ID AND RTC.COLUMN_ID = FKC.REFERENCED_COLUMN_ID
WHERE O.TYPE_DESC = ‘FOREIGN_KEY_CONSTRAINT’
Another big difficulty at the time of copying a database to another location are the fields with “IDENTITY INCREMENT”
How about this..
SELECT TC.CONSTRAINT_NAME,TC.TABLE_NAME ,
KCU.COLUMN_NAME,ORDINAL_POSITION AS COLUMN_POSITION
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC, INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU
WHERE 1=1
AND TC.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME
AND TC.TABLE_NAME = KCU.TABLE_NAME
AND TC.CONSTRAINT_TYPE = ‘FOREIGN KEY’
dont know if its correct…
may be this…
SELECT
TC.CONSTRAINT_NAME AS FOREIGN_KEY_CONSTRAINT_NAME,
TC.TABLE_NAME AS FOREIGN_KEY_TABLE_NAME,
KCU.COLUMN_NAME,
ORDINAL_POSITION AS COLUMN_POSITION,
RC.UNIQUE_CONSTRAINT_NAME AS PARENT_PRIMARY_KEY_NAME,
TC_PK.TABLE_NAME AS PARENT_TABLE_NAME
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU,
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC,
INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC_PK
WHERE TC.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME
AND TC.TABLE_NAME = KCU.TABLE_NAME
AND TC.CONSTRAINT_TYPE = ‘FOREIGN KEY’
AND RC.CONSTRAINT_NAME = TC.CONSTRAINT_NAME
AND RC.UNIQUE_CONSTRAINT_NAME = TC_PK.CONSTRAINT_NAME
Hi All,
When you genarate script through wizard. choose the options Script Indexes also to True. Then choose tables. It will genarate all constraints automatically including IDENTITY INCREMENT.
There is no need to take script especially for primary keys and foreign keys.
Thank you,
Ganga.
The biggest problem is: (SQL1 for copying SQL2)
If creating the database in SQL2 (with the script generated by the wizard SQL1), including IDENTITY INCREMENT, when you move a database to another, if the fields are not in sequence (1,2,3,4 , 5 ,…) ie the ID’s are out of sequence (eg an ID that was deleted) … at the time of the copy, you have a logical sequence of numbers … not following the real ID which is the original base … I did understand?
Dear Sir / Madam,
I have a pleasure to mail to you.
Please go through my concept to give you better ideas and I shall gain
knowledge from you kind people :-
Tablename : ————————-> T1
Rollno_Class-X ———>PK
Tablename : ————————-> T2
Rollno_Class-XII ———>PK
Tablename : ————————-> T3
Rollno_Class-Degree ———>PK
In table——> T1 , T2 and T3 I have only the rollno’s of the candidate. In these table PK’s has been
defined by not using the syntax of primary key.I have defined it in my way but it follows the rule of
unique and not null both.
Tablename : ————————-> Result_Details1
Rollno_Class-X ———>FK
Tablename : ————————-> Result_Details2
Rollno_Class-XII ———>FK
Tablename : ————————-> Result_Details3
Rollno_Class-Degree ———>FK
Now, my table—–> Result_Details1, Result_Details2, Result_Details3 which is the foreign key of
above respective PK cols fields have other details also.
Tablename : ————————-> Records
Rollno_Class-X ———>PK Rollno_Class-XII ———>PK Rollno_Class-Degree ———>PK
PK has been defined as in table—–> T1, T2 and T3.Now, my Table—> Records contains only
Rollno_Class-X ,Rollno_Class-XII and Rollno_Class-Degree. Which is my PK ? Pls
@shekhar sinha
I am having a hard time understanding the TABLE structure. Please post the actual SQL for the TABLE definitions.
I think best Will be to Use DBPRO ( VSTS Database Edition)to Manage the Database project. DBPRO has a Schema compare feature by which you can get all the primary and foreign key in to the project and deploy it anywhere you wish to.
— 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