Technical Article

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)

You rated this post out of 5. Change rating

Share

Share

Rate

3.57 (7)

You rated this post out of 5. Change rating