Below is my CTE effort, which won't work on SQL2000, but which does consider multiple fkeys.

I'm not entirely sure what the 'standard' convention is, as it seems to depend on how you create your foreign keys. I think it might even have changed between releases / service packs. But the essential elements have remained...i.e...pktable, fktable and numeric counter. The script below should be quite simple to customise to taste.

Regards,

David.

;with keycouples as

(

SELECT fkeyid, rkeyid, count(*) as keycount

FROM sys.sysreferences

GROUP BY fkeyid, rkeyid

),

referencerows as

(

select

refs.constid as constid, refs.fkeyid, refs.rkeyid, row_number() over (partition by refs.fkeyid,refs.rkeyid order by refs.fkeyid , refs.rkeyid) as keynum, coup.keycount

FROM sys.sysreferences refs INNER JOIN keycouples coup on refs.fkeyid = coup.fkeyid and refs.rkeyid = coup.rkeyid

),

chunks as

(

select

quotename(quotename(object_schema_name(rr.fkeyid))+'.'+quotename(object_name(rr.constid)),'''') as objname,

object_name(rr.rkeyid)+'_'+object_name(rr.fkeyid) +case when rr.keycount=1 then '' else '_FK'+cast(rr.keynum as varchar) end as newname

FROM referencerows rr

)

SELECT 'exec sp_rename ' + objname + ','+quotename(newname,'''')+','+quotename('OBJECT','''')+' GO' FROM chunks

order by objname