Is there a standard for naming foreign keys when two columns reference the same table? I've always used the name FK_CurrentTableName_ReferencedTableName. I just ran into a situation where I've got a table with CreatedById and ModifiedById both pointing to the same (users) table, so this naming convention won't work.

Is it actually better to change all my reference constraints to FK_CurrentTableName_CurrentColumnName? Or should I keep it the same unless there is a conflict and then do FK_CurrentTableName_CurrentColumnName_ReferencedTableName or FK_CurrentTableName_ReferencedTableName_CurrentColumnName?

I've searched and read contradictory opinions. For SQL Server, is there a standard?
No, there is no standard naming convention for this or just about any other scenario. Naming conventions are purely subjective: that your convention makes sense, and that you use your standard consistently, are both much more important than which standard you choose.

That said, in this case, I would opt for this option:

FK_CurrentTable_Created_Users
FK_CurrentTable_Modified_Users


This provides all the information you would need if you wanted to deduce something about this foreign key without all the difficult labor of retrieving the actual definition. If you just use this:

FK_CurrentTable_Created


Then you don't get any information about the other table. In this specific case it should be easy enough to infer, but that won't always be true. If you use this:

FK_CurrentTable_Users_Created


That just feels to me like it's in the wrong order. "This table references that table on column x." Much more intuitive, IMHO, to say "This table's column x references that table." It might even call for full references on both sides (of course, the users table may have more than one candidate key, though it is fairly uncommon for an FK to point to a candidate/natural rather than the surrogate):

FK_CurrentTable_Created_Users_UserID


And therein lies the problem. You're going to continue finding contradictory opinions precisely because this decision has to, by definition, be driven by opinion. And not everyone is going to agree.