How to Script Out all The Foreign Keys of a Table
November 17th, 2007 by Andrew Chen
I encountered a problem at work when I was requested to set up one way transactional replication between two databases. The subscribing database already have the tables of the replication and there were many tables in the subscribing database that have foreign keys referencing the replicating tables. So guess what? The initial push of the replication snap shot failed. Because the intial push of the publication snap shot was trying to drop the existing tables and recreate them.
The script has two part. The first part is to generate the drop statements to drop the foreigns keys that referece certain tables. The second part generates the statements to create the foreign keys. The script is writing on SQL server 2005 and it has limitation. It didn’t consider table schema and it doesn’t work on composite foreign keys. If anyone want a SQL 2000 verion or want one that is able to work on composite keys then let me know. I will see if I can help.
select 'ALTER TABLE '+object_name(a.parent_object_id)+
' DROP CONSTRAINT '+ a.name
from sys.foreign_keys a
join sys.foreign_key_columns b
on a.object_id=b.constraint_object_id
join sys.columns c
on b.constraint_column_id = c.column_id
and b.parent_object_id=c.object_id
join sys.columns d
on b.referenced_column_id = d.column_id
and b.referenced_object_id = d.object_id
where object_name(b.referenced_object_id) in
('tablename1','tablename2')
order by c.name
select 'ALTER TABLE '+object_name(a.parent_object_id)+
' ADD CONSTRAINT '+ a.name +
' FOREIGN KEY (' + c.name + ') REFERENCES ' +
object_name(b.referenced_object_id) +
' (' + d.name + ')'
from sys.foreign_keys a
join sys.foreign_key_columns b
on a.object_id=b.constraint_object_id
join sys.columns c
on b.parent_column_id = c.column_id
and a.parent_object_id=c.object_id
join sys.columns d
on b.referenced_column_id = d.column_id
and a.referenced_object_id = d.object_id
where object_name(b.referenced_object_id) in
('tablename1','tablename2')
order by c.name
You should replace tablename with the name of the table you want to script foreign keys
Related Posts:
(2 votes, average: 5 out of 5)
Loading ...13 Comments
Sorry, the comment form is closed at this time.
This absolutely is brilliant - many many thanks - it really saved a lot of headache. Bravo!!!
Thanks a bunch! Great script.
This is a great script. Replication is the worst. I recently replicated a database from a publisher to a subscriber, not knowing that the identity values of the subscriber are not incremented. I had to create a script to reseed all the identity values of the subscriber database. Now, I’m seeing my foreign keys didn’t replicate, so I have to reproduce those manually.
Thanks for this!
Thank you very much. You just saved me a lot of time.
I check comments on my blog this morning and and found many positive comments on this post. I am really glad to see it helps.
Is there any way to find Missing Foriegn Keys in a SQL SERVER 2005 Database.
i got these error messages
Msg 102, Level 15, State 1, Line 754
Incorrect syntax near ‘.’.
Msg 102, Level 15, State 1, Line 758
Incorrect syntax near ‘.’.
Msg 102, Level 15, State 1, Line 770
Incorrect syntax near ‘.’.
i got these errors:
Msg 102, Level 15, State 1, Line 572
Incorrect syntax near ‘.’.
Msg 102, Level 15, State 1, Line 579
Incorrect syntax near ‘.’.
Msg 102, Level 15, State 1, Line 580
Incorrect syntax near ‘.’.
Msg 102, Level 15, State 1, Line 585
Incorrect syntax near ‘.’.
Msg 102, Level 15, State 1, Line 592
Incorrect syntax near ‘.’.
Msg 102, Level 15, State 1, Line 612
Incorrect syntax near ‘.’.
Msg 102, Level 15, State 1, Line 619
Incorrect syntax near ‘.’.
Msg 102, Level 15, State 1, Line 655
Incorrect syntax near ‘.’.
Msg 102, Level 15, State 1, Line 694
Incorrect syntax near ‘.’.
Msg 102, Level 15, State 1, Line 695
Incorrect syntax near ‘.’.
Msg 102, Level 15, State 1, Line 705
Incorrect syntax near ‘.’.
Msg 102, Level 15, State 1, Line 743
Incorrect syntax near ‘.’.
Msg 102, Level 15, State 1, Line 754
Incorrect syntax near ‘.’.
Msg 102, Level 15, State 1, Line 758
Incorrect syntax near ‘.’.
Msg 102, Level 15, State 1, Line 770
Incorrect syntax near ‘.’.
Thanks man. That is very useful
Absolutely perfect. Was going to break my head with management studio. Good thing I looked. This took fraction of a second to generate what the scripts.
Thanks much.
great script. but does not work if there are multiple columns in the foreign key. it returns me 2 drop statement (which is okay) but two create statements - one for each column like this:
ALTER TABLE TABLE_NAME ADD CONSTRAINT FK__TABLE_NAME__SOMENAME FOREIGN KEY (COLUMN_1) REFERENCES CUSTOMER (COLUMN_1)
ALTER TABLE TABLE_NAME ADD CONSTRAINT FK__TABLE_NAME__SOMENAME FOREIGN KEY (COLUMN_2) REFERENCES CUSTOMER (COLUMN_2)
any way to get them all in one statemenet?
Thanks! I am building an SSIS package to refresh a test database with production data. Since the process involves truncating the tables and the database has 52 foreign keys, I needed a way to retrieve all the pertinent values for those keys and save them in a table so the package can drop all the foreign keys in the first step of the package and then recreate the foreign keys in the package’s last step. I was close but hadn’t worked out where the column names were. Your post saved me several hours.
–Drop and Recreate Foreign Key Constraints
SET NOCOUNT ON
DECLARE @table TABLE(
RowId INT PRIMARY KEY IDENTITY(1, 1),
ForeignKeyConstraintName NVARCHAR(200),
ForeignKeyConstraintTableSchema NVARCHAR(200),
ForeignKeyConstraintTableName NVARCHAR(200),
ForeignKeyConstraintColumnName NVARCHAR(200),
PrimaryKeyConstraintName NVARCHAR(200),
PrimaryKeyConstraintTableSchema NVARCHAR(200),
PrimaryKeyConstraintTableName NVARCHAR(200),
PrimaryKeyConstraintColumnName NVARCHAR(200)
)
INSERT INTO @table(ForeignKeyConstraintName, ForeignKeyConstraintTableSchema, ForeignKeyConstraintTableName, ForeignKeyConstraintColumnName)
SELECT
U.CONSTRAINT_NAME,
U.TABLE_SCHEMA,
U.TABLE_NAME,
U.COLUMN_NAME
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE U
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C
ON U.CONSTRAINT_NAME = C.CONSTRAINT_NAME
WHERE
C.CONSTRAINT_TYPE = ‘FOREIGN KEY’
UPDATE @table SET
PrimaryKeyConstraintName = UNIQUE_CONSTRAINT_NAME
FROM
@table T
INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS R
ON T.ForeignKeyConstraintName = R.CONSTRAINT_NAME
UPDATE @table SET
PrimaryKeyConstraintTableSchema = TABLE_SCHEMA,
PrimaryKeyConstraintTableName = TABLE_NAME
FROM @table T
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C
ON T.PrimaryKeyConstraintName = C.CONSTRAINT_NAME
UPDATE @table SET
PrimaryKeyConstraintColumnName = COLUMN_NAME
FROM @table T
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE U
ON T.PrimaryKeyConstraintName = U.CONSTRAINT_NAME
–SELECT * FROM @table
–DROP CONSTRAINT:
SELECT
‘
ALTER TABLE [’ + ForeignKeyConstraintTableSchema + ‘].[’ + ForeignKeyConstraintTableName + ‘]
DROP CONSTRAINT ‘ + ForeignKeyConstraintName + ‘
GO’
FROM
@table
–ADD CONSTRAINT:
SELECT
‘
ALTER TABLE [’ + ForeignKeyConstraintTableSchema + ‘].[’ + ForeignKeyConstraintTableName + ‘]
ADD CONSTRAINT ‘ + ForeignKeyConstraintName + ‘ FOREIGN KEY(’ + ForeignKeyConstraintColumnName + ‘) REFERENCES [’ + PrimaryKeyConstraintTableSchema + ‘].[’ + PrimaryKeyConstraintTableName + ‘](’ + PrimaryKeyConstraintColumnName + ‘)
GO’
FROM
@table
GO