Thursday, July 23, 2009

Generate Foreign Key for All Databases

sp_MSforeachdb
'USE ?
IF DB_ID(''?'') > 4 -- Skip system databases
BEGIN
EXEC (''
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)+'''')'''' as Scripts
FROM .sys.foreign_keys AS f
INNER JOIN .sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id
'')
END'


No comments:

Post a Comment

All suggestions are welcome