Script to Generate All Primary and Foreign Keys

--***********Generate create script for all Primary Keys

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 ('

-- 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 + ')' + ' ON '+@FileName

-- Print the primary key statement

PRINT @PKSQL

FETCH NEXT FROM cPK INTO @PkTable, @PkName, @FileName

END

CLOSE cPK

DEALLOCATE cPK

---*********Generate create script for all Foreign Keys

SELECT 'ALTER TABLE '+OBJECT_NAME(F.PARENT_OBJECT_ID)+ ' ADD CONSTRAINT'

+ F.NAME + ' FOREIGN KEY'+'('+COL_NAME(FC.PARENT_OBJECT_ID,FC.PARENT_COLUMN_ID)+

')'+'REFRENCES '+OBJECT_NAME (F.REFERENCED_OBJECT_ID)+'('

+COL_NAME(FC.REFERENCED_OBJECT_ID,FC.REFERENCED_COLUMN_ID)+')'

FROM SYS.FOREIGN_KEYS AS F

INNER JOIN SYS.FOREIGN_KEY_COLUMNS AS FC

ON F.OBJECT_ID = FC.CONSTRAINT_OBJECT_ID