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.

Solarwinds

SQL SERVER - Puzzle - Write Script to Generate Primary Key and Foreign Key pkgen

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)

Solarwinds

,
Previous Post
SQLAuthority News – SQL Server Value Calculator
Next Post
SQL SERVER – Maximizing View of SQL Server Management Studio – Full Screen – New Screen

Related Posts

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

    Reply
  • shekhar sinha,Guwahati, Assam, India
    August 17, 2009 9:53 am

    Dear Sir,

    I hv a pleasure to mail to you.

    Still I’m not satisfied.

    Shekhar Sinha, Guwahati, Assam , India

    Reply
  • 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.

    Reply
  • Hi Pinal,

    I updated my script and added code to handle ascending/descending sort order and to incorporate a suggestion from a reader.

    Reply
  • Pinal,

    I wrote a script to create all foreign keys:

    Reply
  • very good information on sql server

    Reply
  • so…..is there a reliable script to create all primary keys?

    Reply
  • I clicked on John Paul’s link to the script and get a display page error.

    Reply
  • 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.

    Reply
  • 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

    Reply

Leave a Reply

Menu