RealWorldDevelopers
9/19/2016 - 11:21 PM

Script all Primary Keys in a SQL Database

Script all Primary Keys in a SQL 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 [dbo].['    +   @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   +    '] ASC)'    +    ' ON ['   +   @FileName   +    ']'  
 -- Print the primary key statement  
 PRINT @PKSQL  

 FETCH    NEXT    FROM   cPK   INTO   @PkTable, @PkName, @FileName  
 END  
 CLOSE   cPK  
 DEALLOCATE   cPK