Hi,
Here is a sample report which will draw an entity-relationship diagram from your database using system views. The output of the report looks like this:
Sample report from sakila database
Views and tables with no referential integrity constrains will show up with no connections.
CREATE PROCEDURE sp_DBR_schema( in_schema varchar(64 ) )
BEGIN select 'dbr.chart', 'Hierarchy', CONCAT( in_schema, ' @ ', CURDATE(), ' ', CURTIME() );
select 'dbr.chart.options', 'format', 'pdf'; select 'dbr.chart.gv.style', 'graph', 'compound=true, fontname="Arial", ranksep=1.0, ratio=0.8, rankdir="LR"';
select 'dbr.chart.gv.style', 'node', 'shape="plaintext", fontname="Arial", fontsize=10';
select 'dbr.chart.gv.style', 'cluster', 'shape=none, color=white';
select 'dbr.chart.gv.style', 'edge', 'arrowhead=none, arrowtail=normal, style=dashed, color="#444444", '; select 'dbr.chart.gv.node', table_name, fn_table_info( in_schema, table_name ), '', concat('c_', t.table_name)
from information_schema.tables t
where t.table_schema = in_schema; select 'dbr.chart.gv.cluster', concat('c_', t.table_name), '', 'color=white'
from information_schema.tables t
where t.table_schema = in_schema; select table_name, null
from information_schema.tables t
where t.table_schema = in_schema
union
select concat(referenced_table_name, ':', referenced_column_name), concat(table_name, ':', column_name)
from information_schema.key_column_usage
where table_schema=in_schema and referenced_table_name is not null; END
--
myDBR Team