How can I execute JavaScript on a SFDC Standard Detail Page?

The only way we found to insert code directly into the DOM of any Salesforce page is via a Home Page Component (Setup > Customize > Home > Home Page Components).

Create a New Custom Component, Type = HTML Area. In the editor, check the "Show HTML" checkbox in the upper right. You can then put in arbitrary HTML and JavaScript and it can traverse the DOM.

If you just want your code on a single page, create a "Wide (Right) Column" component and add it to your page layout.

If you want the code to run on every* page in SFDC, then you'll want to put it into a "Narrow (Left) Column" (often referred to as the sidebar) component, check the Setup > Customize > User Interface > Show Custom Sidebar Components on All Pages checkbox, and finally select it in Setup > Customize > Home > Home Page Layouts > your layout.

Home Page Components don't need to have any visible UI.

• Chatter pages don't have the sidebar, so this won't work there. The Service Cloud Console also doesn't show the sidebar and has no equivalent, so you can't do this there either.

Start by creating a VF page that includes the dialog, and uses the standard controller so you can drop it into the object's whose details your trying to add it to the page layout, since you didn't specify, I'll just use 'Account'

You need to uploaded your jQuery or whatever JS libraries your using to generate the dialog into a static resource file (as you can't remotely use a CDN on the standard details page)

<apex:page standardController="account">
<apex:includeScript value="{!URLFOR($Resource.jquery)}" />
<script type="text/javascript">
$(document).ready(function(){
    $('#open-notes-dialog).click(function(){
        alert('hello world');
    });        

});
</script>
<button id="open-notes-dialog">Add notes</button>

Add the page to the layout of your details page. Unfortunately, its kind of hard to get it to display in a correct size.

However, you will now have this VF page in an iFrame and with some creative JS can go outside of your iframe (into the parent) and rename the 'Notes & Attachments' heading, and add your dialog!

Update -- looks like you can't access static resources from the standard details page- so maybe add the javascript you need to your page statically but adding a dynamic library might be limited.

Another approach to this problem is to load your JavaScript using the {!REQUIRESCRIPT} function in a custom button/link on the page.
More info here: Changing the color of a custom button