Last active
April 27, 2016 15:44
-
-
Save corburn/7e23af9bd82b4ccdbe36 to your computer and use it in GitHub Desktop.
This script was created to visually answer the question which tables/fields in a database are actually being used. It uses Laravel and Canvas.js to create a webpage full of bar charts for each table, in ascending order by number of records in the table, showing all the fields present in the table and how many records have a non-null value in the…
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| <!DOCTYPE HTML> | |
| <html> | |
| <head> | |
| <script type="text/javascript"> | |
| window.onload = function () { | |
| @foreach($tables as $table => $columns) | |
| var chart = new CanvasJS.Chart("{{$table}}", | |
| { | |
| theme: "theme3", | |
| animationEnabled: true, | |
| title:{ | |
| text: "{{$table}}", | |
| fontSize: 30 | |
| }, | |
| toolTip: { | |
| shared: true | |
| }, | |
| axisY: { | |
| title: "Defined values", | |
| }, | |
| axisX: { | |
| title: "Column names", | |
| labelAutoFit: true, | |
| //labelAngle: 50 | |
| }, | |
| data: [ | |
| { | |
| type: "column", | |
| name: "Defined Values", | |
| dataPoints:[ | |
| @foreach($columns as $column => $structure) | |
| {label: "{{$column}}", y: {{$structure['Defined']}}}, | |
| @endforeach | |
| ] | |
| }, | |
| ], | |
| }); | |
| chart.render(); | |
| @endforeach | |
| } | |
| </script> | |
| {{HTML::script('https://cdnjs.cloudflare.com/ajax/libs/canvasjs/1.7.0/canvasjs.min.js')}} | |
| </head> | |
| <body> | |
| @foreach($tables as $table => $columns) | |
| <div id="{{$table}}" style="height: 300px; width: 100%;"> | |
| </div> | |
| @endforeach | |
| </body> | |
| </html> |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| $result = []; | |
| $database_name = 'foo' | |
| $connection_name = 'bar'; | |
| $db = DB::connection($connection_name); | |
| /* | |
| * { | |
| * Tables_in_pims_test: p_user | |
| * } | |
| */ | |
| $tables = array_column($db->select('SHOW TABLES'), 'Tables_in_' . $database_name); | |
| foreach($tables as $table_name) { | |
| /* | |
| * { | |
| * Field: "id", | |
| * Type: "int(10) unsigned", | |
| * Null: "NO", | |
| * Key: "PRI", | |
| * Default: null, | |
| * Extra: "auto_increment" | |
| * }, | |
| */ | |
| $columns = $db->select('SHOW COLUMNS FROM ' . $table_name); | |
| $total_rows = $db->table($table_name)->count(); | |
| foreach($columns as $column) { | |
| if ($column['Null'] === 'YES') { | |
| $column['Defined'] = $db->table($table_name)->whereNotNull($column['Field'])->count(); | |
| $result[$table_name][$column['Field']] = $column; | |
| } else { | |
| $column['Defined'] = $total_rows; | |
| $result[$table_name][$column['Field']] = $column; | |
| } | |
| } | |
| } | |
| return \Response::json($result); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment