SQL SERVER – Puzzle – Write Script to Generate Primary Key and Foreign Key

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 (http://blog.SQLAuthority.com)

About these ads

35 thoughts on “SQL SERVER – Puzzle – Write Script to Generate Primary Key and Foreign Key

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

    • hi Aasim,

      No, you do not have to recreate the wheel. However, your pointed script is not complete.

      Please try that script on Adventureworks database again. I think it is not giving appropriate answers. It also brings back check contrains.

      Additionally, it is not taking care of ORDER of Primary Key and on which filegroup it is created.

      Kind Regards,
      Pinal

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

    • You are correct Kuldip,

      Your suggestion will work for CREATE but not the ALTER script.

      I truly appreciate your participation. Please continue with your valuable contribution.

      Kind Regards,
      Pinal

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

  4. 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 ?

  5. Another big difficulty at the time of copying a database to another location are the fields with “IDENTITY INCREMENT”

  6. – 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

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

  8. –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’

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

  10. 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’

  11. Another big difficulty at the time of copying a database to another location are the fields with “IDENTITY INCREMENT”

  12. 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…

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

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

  15. 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?

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

  17. @shekhar sinha

    I am having a hard time understanding the TABLE structure. Please post the actual SQL for the TABLE definitions.

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

  19. Hello All,

    I was just going over scripts posted as answer to this question.

    So far for Primary Key only one script is close which is of ‘aasim’.

    And for foreign key the script which is close is ‘Aman’.

    There are two things which are missing in aasim’s query
    1) FileGrouop which actually the table belongs to as it is hardcoded at this moment.
    2) The direction of PK column i.e. ASC or DESC

    Let me know if any other expert have other idea.

    Kind Regards,
    Pinal

  20. – 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

  21. Hi Pinal,

    I needed to script primary key creation today at work, so I wrote this script:

    http://sqlblog.com/blogs/john_paul_cook/archive/2009/09/16/script-to-create-all-primary-keys.aspx

    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.

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

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

  24. Pingback: SQL SERVER – Weekly Series – Memory Lane – #039 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s