REBOL [ Title: "SQL Server table-column cross reference" Purpose: {Use some control tables in an SQL database to cross reference the tables in the database with the columns in those tables.} ] ;; [---------------------------------------------------------------------------] ;; [ This program uses tables in an SQL Server data base to obtain the ] ;; [ table names and column names from the data, and formats an ] ;; [ html cross reference of those items. ] ;; [ You will have to set up an ODBC connection to an SQL Server database. ] ;; [ Functions for "opening" and "closing" a database are provided below ] ;; [ so you can find them and tailor them for your installation. ] ;; [---------------------------------------------------------------------------] DB-OPEN: does [ DB-CON: open odbc://datasourcename:password@userid DB-CMD: first DB-CON ] DB-CLOSE: does [ close DB-CMD ] ;; [---------------------------------------------------------------------------] ;; [ These are functions we will use to write the report to an HTML file. ] ;; [---------------------------------------------------------------------------] HTMLFILE-PAGE: make string! 5000 HTMLFILE-FILE-ID: %htmlfile.htm HTMLFILE-OPEN-OUTPUT: does [ HTMLFILE-PAGE: copy "" ] HTMLFILE-CLOSE: does [ write HTMLFILE-FILE-ID HTMLFILE-PAGE ] HTMLFILE-EMIT: func [ HTMLFILE-LINE ] [ append repend HTMLFILE-PAGE HTMLFILE-LINE newline ] HTMLFILE-EMIT-FILE: func [ HTMLFILE-TEMPLATE [file!] ] [ HTMLFILE-EMIT build-markup read HTMLFILE-TEMPLATE ] ;; [---------------------------------------------------------------------------] ;; [ Reassure that the program is working. ] ;; [---------------------------------------------------------------------------] alert "This will run silently and take a minute." ;; [---------------------------------------------------------------------------] ;; [ These are the SQL commands we will submit, one after the other, ] ;; [ to make the two parts of the report. ] ;; [---------------------------------------------------------------------------] SQL-COMMAND-TABLES: { select TABLE_NAME ,COLUMN_NAME from information_schema.columns order by TABLE_NAME, ORDINAL_POSITION, COLUMN_NAME } SQL-COMMAND-COLUMNS: { select COLUMN_NAME ,TABLE_NAME from information_schema.columns order by COLUMN_NAME, TABLE_NAME } ;; [---------------------------------------------------------------------------] ;; [ These are the html fragments we will assemble into the final page. ] ;; [---------------------------------------------------------------------------] XREF-HEAD: { Table-field cross reference

Table-field cross reference

} XREF-FOOT: { } XREF-TC-HEAD: {

Tables and their columns

} XREF-TC-FOOT: {
Table Columns
} XREF-TC-BODY-HEAD: { <% WS-TABLE %> } XREF-TC-BODY-FOOT: { } XREF-TC-BODY-COL: { <% WS-COLUMN %> , } ;; ----------------------------------------------------------------------------- XREF-CT-HEAD: {

Columns and their tables

} XREF-CT-FOOT: {
Column Tables
} XREF-CT-BODY-HEAD: { <% WS-COLUMN %> } XREF-CT-BODY-FOOT: { } XREF-CT-BODY-COL: { <% WS-TABLE %> , } ;; [---------------------------------------------------------------------------] ;; [ As we get table names and column names from our query, we will store ] ;; [ them here for the build-markup command. ] ;; [---------------------------------------------------------------------------] WS-TABLE: "" WS-COLUMN: "" ;; [---------------------------------------------------------------------------] ;; [ As we read through the output of the query, we will have to do control ] ;; [ breaks. For example, when doing the table-column list, when the table ] ;; [ changes we will have to start a new row. So we will need places to ] ;; [ hold the table we are working on so we can check each incoming table ] ;; [ name against it. ] ;; [---------------------------------------------------------------------------] HOLD-TABLE: "" HOLD-COLUMN: "" ;; [---------------------------------------------------------------------------] ;; [ "Open" the html file. ] ;; [ Write the headers and such. ] ;; [---------------------------------------------------------------------------] HTMLFILE-FILE-ID: %table-column-xref.html HTMLFILE-OPEN-OUTPUT HTMLFILE-EMIT XREF-HEAD ;; [---------------------------------------------------------------------------] ;; [ Open the database. ] ;; [---------------------------------------------------------------------------] DB-OPEN ;; [---------------------------------------------------------------------------] ;; [ Make the table-column part of the page. ] ;; [---------------------------------------------------------------------------] FORMAT-TABLE-LINE: does [ WS-TABLE: copy "" WS-COLUMN: copy "" WS-TABLE: trim to-string first SQL-RESULT WS-COLUMN: trim to-string second SQL-RESULT if not-equal? WS-TABLE HOLD-TABLE [ if not-equal? HOLD-TABLE "" [ HTMLFILE-EMIT XREF-TC-BODY-FOOT ] HOLD-TABLE: copy WS-TABLE HTMLFILE-EMIT build-markup XREF-TC-BODY-HEAD ] HTMLFILE-EMIT build-markup XREF-TC-BODY-COL ] HOLD-TABLE: copy "" HOLD-COLUMN: copy "" HTMLFILE-EMIT XREF-TC-HEAD SQL-RESULT: copy [] insert DB-CMD SQL-COMMAND-TABLES while [SQL-RESULT: pick DB-CMD 1] [ FORMAT-TABLE-LINE ] HTMLFILE-EMIT XREF-TC-BODY-FOOT HTMLFILE-EMIT XREF-TC-FOOT ;; [---------------------------------------------------------------------------] ;; [ Make the column-table part of the page. ] ;; [---------------------------------------------------------------------------] FORMAT-COLUMN-LINE: does [ WS-TABLE: copy "" WS-COLUMN: copy "" WS-COLUMN: trim to-string first SQL-RESULT WS-TABLE: trim to-string second SQL-RESULT if not-equal? WS-COLUMN HOLD-COLUMN [ if not-equal? HOLD-COLUMN "" [ HTMLFILE-EMIT XREF-CT-BODY-FOOT ] HOLD-COLUMN: copy WS-COLUMN HTMLFILE-EMIT build-markup XREF-CT-BODY-HEAD ] HTMLFILE-EMIT build-markup XREF-CT-BODY-COL ] HOLD-TABLE: copy "" HOLD-COLUMN: copy "" HTMLFILE-EMIT XREF-CT-HEAD SQL-RESULT: copy [] insert DB-CMD SQL-COMMAND-COLUMNS while [SQL-RESULT: pick DB-CMD 1] [ FORMAT-COLUMN-LINE ] HTMLFILE-EMIT XREF-CT-BODY-FOOT HTMLFILE-EMIT XREF-CT-FOOT ;; [---------------------------------------------------------------------------] ;; [ Close the database. ] ;; [---------------------------------------------------------------------------] DB-CLOSE ;; [---------------------------------------------------------------------------] ;; [ Finish the html file. ] ;; [---------------------------------------------------------------------------] HTMLFILE-EMIT XREF-FOOT HTMLFILE-CLOSE ;; [---------------------------------------------------------------------------] ;; [ Inform when done. ] ;; [---------------------------------------------------------------------------] alert "Done."