数据库设计的静态分析工具

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.

我正在寻找数据库层的静态分析工具。我得到了一些用于查看PLSQL,TSQL代码的答案,我想知道有哪些选项可用于查看表及其列的命名约定,外键约束和触发器等的数据库设计。

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

有一篇关于ApexSQL Enforce的MSDN文章,但它主要用于SQLServer。

2 个解决方案

#1

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

不是一个工具,但一个好的资源是更好的SQL Server数据库的SSW规则

#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:

既然你提到PLSQL,我假设你使用的是Oracle。对于数据库设计,您可以编写一组主要在数据字典表上运行的简单SQL脚本。请查看以下示例规则和相同的sql:

Table name should not be greater than 'N' characters

表名不应大于'N'个字符

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

列名不应超过'N'字符

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)

列出表的所有VALID外键列(假设您使用的是fk约束)

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.

如果您没有使用外键约束来提高性能,则需要将元信息存储在单独的表中,并编写如上所述的ansql脚本,以检查现有数据的FK违规。

#1