SQL Server 2005: Script all Primary Keys

,

Since it is not possible to script only the primary keys of all tables with the SQL Server 2005 Management Studio, here a small script to get the create scripts for all existing primary keys of the current database.

-- Get all existing primary keys
DECLARE cPK CURSOR FOR
   SELECT TABLE_NAME, CONSTRAINT_NAME 
   FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
   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 @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
END
CLOSE cPK
DEALLOCATE cPK

Rate

3.57 (7)

Share

Share

Rate

3.57 (7)