I have used Sharepoint designer 2010 to make SQL server tables available as external content types (and lists) for a site collection but what about across multiple site collections--can this be done?
External content types are available at farm level so you should have no problems accessing them across site collections. Just make sure you have the permissions set correctly.
My understanding is that the Managed Metadata functionality will allow you to publish your content types across site collections. However, it doesn't appear that the Managed Metadata supports external content types just yet. Seems that the only option at this point would be a script to replicate the external list across your site collections.