Are there useful static analysis tools for databases?

Question

Is there a tool for examining the configuration and schema of a database for dubious fields, relationships and configuration, similar to how static analysis tools like lint will flag dubious lines of code?

I'm not necessarily asking for normalization, but surely there's stupid stuff that can be detected without solving Hard AI or the Halting Problem.

Answer 1

Check out DBMain. This is used to analyze and transform database models.

Answer 2

Not sure if this does absolutely everything you are looking for...but all of red gates tools rock!

http://www.red-gate.com/products/SQL%5FDependency%5FTracker/index.htm

Answer 3

We use ClearSQL for PL/SQL. Not spotless, but pretty comprehensive, fast, and I really like the user interface.

Answer 4

I regularly use SqlSpec from www.elsasoft.org
It makes a nice documentation you can navigate (in html or chm formats), and works with most databases.
It's not completely flawless, but it's quite good, affordable, and the author responds emails, which I appreciate.

Answer 5

What database vendor are you targeting?

SQL Server Tool ApexSQL Enforce http://www.apexsql.com/sql%5Ftools%5Fenforce.asp

Answer 6

I've found SchemaSpy to be useful. It generates a schema chart and detailed HTML reports via ODBC. It has an "anomalies" tab in the HTML output that describes some basic questionable designs:

• Tables with a single column
• Tables without a primary key
• Missing foreign key constraints implied by field names
• Implied primary keys
• Columns both 'nullable' and 'must be unique'
• Columns whose default value is the word 'NULL' or 'null', where the SQL NULL value may have been intended

It's been pretty useful. Perhaps more such rules can be added?