This script is part of the SQL Scripts for Microsoft Dynamics GP where I will be posted the scripts I wrote against Microsoft Dynamics GP over the 19 years before I stopped working with Dynamics GP.
This script selects all primary keys for all tables in a database and creates the ALTER script to reapply them. It seems I created this in 2015 to allow primary keys to be exported from one Dynamics GP database and then applied against another.
/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
-- Get all existing primary keys
DECLARE cPK CURSOR FOR
SELECT
so.name,
si.name,
si.type_desc
FROM sys.indexes si
JOIN sys.objects so
ON si.object_id = so.object_id
AND so.type = 'U'
WHERE si.type_desc <> 'HEAP'
AND si.is_Primary_Key = 1
ORDER BY so.Name
DECLARE @PkTable sysname
DECLARE @PkName sysname
DECLARE @KeyType nvarchar(50)
-- Loop through all the primary keys
OPEN cPK
FETCH NEXT FROM cPK INTO @PkTable, @PkName, @KeyType
WHILE (@@FETCH_STATUS = 0)
BEGIN
DECLARE @PKSQL nvarchar(4000)
SET @PKSQL = ''
SET @PKSQL = 'ALTER TABLE ' + @PkTable + ' ADD CONSTRAINT ' + @PkName + ' PRIMARY KEY ' + @KeyType + ' ('
-- 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 + ')'
-- Print the primary key statement
PRINT @PKSQL
FETCH NEXT FROM cPK INTO @PkTable, @PkName, @KeyType
END
CLOSE cPK
DEALLOCATE cPK
