Static Analysis Tools for Database Design

Question

I'm looking for Static Analysis Tools for Database Tier. I got some answers for reviewing PLSQL, TSQL code, i'm wondering what are the options available for reviewing database design for naming conventions of tables and their columns, foreign key constraints and triggers etc.

There is MSDN article which talks about ApexSQL Enforce, but it is primarily for SQLServer.

Answer 1

Not a tool, but a good resource is SSW Rules to Better SQL Server Databases

Answer 2

Since you mention PLSQL, I assume you are using Oracle. For database design, you can write a set of simple SQL scripts that run primarily on the data dictionary tables. Look at the following example rule and the sql for the same:

Table name should not be greater than 'N' characters

DEFINE owner_name = 'SCOTT';
DEFINE max_length = 5; 
set linesize 300;
spool table_name_violations.txt 
Select table_name, length(table_name) Length, 'Table name too long'  MSG
from ALL_TABLES where owner like '&owner_name' 
                      and length (table_name) > &max_length; 
spool off;

Column name should not exceed 'N' Characters

DEFINE owner_name = 'SCOTT';
DEFINE max_length = 5; 
set linesize 300;
spool column_name_violations.txt 
Select table_name, column_name, length(column_name) Length, 'column name too long'  MSG
from ALL_TAB_COLUMNS where owner like '&owner_name' 
                      and length (column_name) > &max_length; 
spool off;

List all VALID foreign key columns of a table (assuming you are using fk constraints)

Define tab_name = 'EMP'
SELECT table_name, Column_name 
FROM user_tab_columns t1
WHERE NOT EXISTS (
      SELECT table_name, column_name
      FROM user_cons_columns 
      WHERE constraint_name IN (
            SELECT R_Constraint_name 
            FROM all_constraints t2,all_cons_columns t3 
            WHERE t2.constraint_name = t3.constraint_name 
            AND t3.column_name = t1.column_name 
            AND t2.constraint_type = 'R'
            AND t2.TABLE_name=t1.Table_name)
       ) 
       AND t1.table_name LIKE '&tab_name'

In the event you are not using foreign key constraints to improve performance, you need to store meta information in separate tables and write ansql script like the one above to check for FK violations on existing data.