I need to create an entity diagram of a MSSQL 2005 database.
Relationships are defined by using primary keys consistently, but there are no foreign keys anywhere. I tried Microsoft Visio's "Reverse Engineer" function, which of course failed due to the lack of foreign keys.
Therefore, I need a diagram tool which doesn't solely rely on foreign keys when drawing relationships.
Create a script which creates the correct foreign key relations, run the diagram tool and then run a second script which drops the foreign keys.
This would allow you to use your tool without disrupting the database too much. If the first script fails, then you'll know that something is wrong with the data, too.
[EDIT] If there is some rule how foreign key columns are named, you can use a scripting language to generate the SQL for you.
If that also fails, any design tool should allow you to create the missing relations. That means you'll probably run into data inconsistencies. The solution here is to make a snapshot of the table definitions and recreate the database (without the data) on a private database server. There, you can mess with the design as much as you want without disrupting the original system.
When you're done with fixing the design, you can extract the commands to create the foreign keys and apply that to the real system -- if you like. That way, you can get a feeling how big the mess in the database already is. If not, then you can simply keep the new copy around, make any design changes there and, after they have been checked, you can migrate the changes to the production database.
In my own systems, I always have scripts to quickly create a clone of the current development and production database. Usually, I use an embedded database like Derby or HSQL. But if you add a filter to the process, you can use $(SCHEMA) in the DDL files and install the same database into different schemas on the same server. We used that with great success during a data migration project where we would save the results of each migration test in a new schema (TABLE_DATE_XX where XX is a two-digit number so you can create more than one test per day).
That allowed us to verify the various fixes, compare two migrations, etc. Since the whole process was 100% automated, it became more cheap to create a new schema than to fix an existing schema.