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

Advertisements

Posted on July 23, 2009, in TSQL Tips n Tricks. Bookmark the permalink. 2 Comments.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: