Script to Generate All Primary in SQL Server

The is very useful when you want to get the definition of all of your primary keys in your SQL Server database.

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 (' 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 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 '[email protected]FileName PRINT @PKSQL FETCH NEXT
FROM cPK INTO @PkTable,
              @PkName,
              @FileName END CLOSE cPK DEALLOCATE cPK