CODEWITHSUNDEEP
database-designcomputer-sciencestatic-analysis
jldugger
jldugger

Reputation: 2469

Are there useful static analysis tools for databases?

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.

Upvotes: 5

Views: 348

Answers (6)

Ira Baxter
Ira Baxter

Reputation: 95334

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

Upvotes: 1

jldugger
jldugger

Reputation: 2469

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?

Upvotes: 0

Kuberchaun
Kuberchaun

Reputation: 30314

What database vendor are you targeting?

SQL Server Tool ApexSQL Enforce http://www.apexsql.com/sql_tools_enforce.asp

Upvotes: 0

iDevlop
iDevlop

Reputation: 25262

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.

Upvotes: 3

reinierpost
reinierpost

Reputation: 8591

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

Upvotes: 0

Andrew Siemer
Andrew Siemer

Reputation: 10278

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_Dependency_Tracker/index.htm

Upvotes: 1

Related Questions