Foreign keys… those practical rules that help maintain data integrity between parent and child tables, allow for table eliminations in queries beginning with Oracle release 10.2.0.1, and provide the potential for the optimizer to generate additional predicates during query optimization for better cardinality estimates.
Those benefits all sound like they might be helpful to someone, but what good are they for the average person (I mean the average developer)? Foreign keys are great for generating abstract art (note that I am a poor judge of art), with a bunch of odd writing all over the place.
This example demonstrates how to query an Excel spreadsheet as if it were a database. The results of the queries are used to build a graphical model of the relationships between the tables based on the foreign key relationships that are established in the database. Excel 2003 limits text boxes to no more than 255 characters, so use Excel 2007 or later for this example, if possible.
First, we need to build the source Excel spreadsheet that will act as the database that will be queried by the second Excel spreadsheet. The source Excel spreadsheet will have two worksheets (tabs) named ”Data Dict Tables” and “Data Dict Foreign Keys”. Use the Microsoft Query Tool, or another approach to bring in the data from the Oracle data dictionary. The queries for each of those worksheets follows:
Data Dict Tables
SELECT DT.OWNER, DT.TABLE_NAME, DTC.COLUMN_NAME, DTC.DATA_TYPE, DTC.DATA_LENGTH, DTC.DATA_PRECISION, DTC.DATA_SCALE, DTC.NULLABLE, DTC.COLUMN_ID, DT.TABLESPACE_NAME, DTCC.COMMENTS TABLE_COMMENTS, SUBSTR(DCC.COMMENTS,1,255) COLUMN_COMMENTS FROM DBA_TABLES DT, DBA_TAB_COLUMNS DTC, DBA_TAB_COMMENTS DTCC, DBA_COL_COMMENTS DCC WHERE DT.OWNER=DTC.OWNER AND DT.TABLE_NAME=DTC.TABLE_NAME AND DT.OWNER=DTCC.OWNER(+) AND DT.TABLE_NAME=DTCC.TABLE_NAME(+) AND DTC.OWNER=DCC.OWNER(+) AND DTC.TABLE_NAME=DCC.TABLE_NAME(+) AND DTC.COLUMN_NAME=DCC.COLUMN_NAME(+) ORDER BY DT.OWNER, DT.TABLE_NAME, DTC.COLUMN_ID;
—
Data Dict Foreign Keys
SELECT DC2.OWNER, DC2.CONSTRAINT_NAME PKEY_CONTRAINT, DC1.CONSTRAINT_NAME FKEY_CONTRAINT, DCC2.TABLE_NAME PKEY_TABLE_NAME, DCC2.COLUMN_NAME PKEY_COLUMN_NAME, DCC1.TABLE_NAME FKEY_TABLE_NAME, DCC1.COLUMN_NAME FKEY_COLUMN_NAME, DCC1.POSITION FROM DBA_CONSTRAINTS DC1, DBA_CONSTRAINTS DC2, DBA_CONS_COLUMNS DCC1, DBA_CONS_COLUMNS DCC2 WHERE DC1.CONSTRAINT_TYPE='R' AND DC1.R_OWNER=DC2.OWNER AND DC1.R_CONSTRAINT_NAME=DC2.CONSTRAINT_NAME AND DC1.OWNER=DCC1.OWNER AND DC1.CONSTRAINT_NAME=DCC1.CONSTRAINT_NAME AND DC2.OWNER=DCC2.OWNER AND DC2.CONSTRAINT_NAME=DCC2.CONSTRAINT_NAME AND DCC1.POSITION=DCC2.POSITION AND DC1.OWNER='SYSADM' ORDER BY DC2.OWNER, DCC2.TABLE_NAME, DCC1.TABLE_NAME, DCC2.POSITION;
The source spreadsheet with the data dictionary details should be saved to the root of the C:\ drive with the name C:\Data Dictionary.xls
In a second spreadsheet we are trying to build the following interface:
Next, we need to add a couple ActiveX controls to the second spreadsheet:
B3: Combo Box with a name of cboTableName with a blank Text value
C3: Check Box with a name of chkAliasNames with a Caption of Alias Table Names
C1: Command Button with a name of cmdInitialize with a Caption of Initialize
A1, A3, B1 – type in the text as shown above
Once we add the macro code, clicking the Initialize button will send a query to the other spreadsheet to retrieve a list of the tables:
And with all of the code in place, selecting a table builds the abstract art:
Running down the left side is the list of columns in the selected table. Every foreign key that is defined against the selected table will trigger a recursive lookup for child tables of the child table, with the necessary join conditions listed as well as the columns that are defined in the child table. For instance, there is a foreign key on the CO_PRODUCT table that points back to the primary key on the PART table, and the join between the tables should be P.ID = CP.PART_ID. The CO_PRODUCT table does not have any child tables.
Skipping down to the CUST_ORDER_LINE table, it has a foreign key that references the PART table on P.ID = COL.PART_ID. The RECEIVABLE_LINE table has a foreign key that references the CUST_ORDER_LINE table on COL.CUST_ORDER_ID = RL.CUST_ORDER_ID AND COL.LINE_NO = RL.CUST_ORDER_LINE_NO. The RECV_LINE_BINARY table has a foreign key that references the RECEIVABLE_LINE table on RL.INVOICE_ID = RLB.INVOICE_ID AND RL.LINE_NO = RLB.RECV_LINE_NO. With this tool we just discovered a way to join the PART table to the RECV_LINE_BINARY by analyzing the defined foreign keys.
OK, enough fun, lets enter the code. Right-click the worksheet name (probably Sheet1) rename the sheet to ExcelQueryOfExcel and then right-click the sheet and to select View Code. Let’s start with making the Initialize button work:
'Need to add a reference to Microsoft ActiveX Data Objects 2.8 Library before starting
'Declare a connection object in the general section to hold the connection to the database
Dim dbExcel As New ADODB.Connection
'Declare a set of variables to hold the username and password for the database
Dim strUserName As String
Dim strPassword As String
Dim strDatabase As String
Private Function ConnectDatabase() As Integer
Dim intResult As Integer
On Error Resume Next
If dbExcel.State <> 1 Then
'Connection to the database if closed
strDatabase = Sheets("ExcelQueryOfExcel").Cells(1, 2).Value
strUserName = ""
strPassword = ""
'Connect to the database
dbExcel.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDatabase & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
dbExcel.ConnectionTimeout = 40
dbExcel.CursorLocation = adUseClient
dbExcel.Open
If (dbExcel.State <> 1) Or (Err <> 0) Then
intResult = MsgBox("Could not connect to the Excel Database." & vbCrLf & Error(Err), 16, "Excel Demo")
ConnectDatabase = False
Else
ConnectDatabase = True
End If
Else
ConnectDatabase = True
End If
End Function
Private Sub cmdInitialize_Click()
Dim intResult As Integer
Dim strSQL As String
Dim strLastTable As String
Dim snpData As ADODB.Recordset
On Error Resume Next
cboTableName.Clear
cboTableName = ""
Sheets("ExcelQueryOfExcel").Range("A6:Z10006").Delete Shift:=xlUp
intResult = ConnectDatabase
strLastTable = ""
If intResult = True Then
Set snpData = New ADODB.Recordset
strSQL = "SELECT" & vbCrLf
strSQL = strSQL & " *" & vbCrLf
strSQL = strSQL & "FROM" & vbCrLf
strSQL = strSQL & " [Data Dict Tables$]" & vbCrLf
strSQL = strSQL & "ORDER BY" & vbCrLf
strSQL = strSQL & " 2" ' Sort the rows by the second column in the data source
snpData.Open strSQL, dbExcel
If snpData.State = 1 Then
Do While Not snpData.EOF
If strLastTable <> snpData("table name") Then
strLastTable = snpData("table name")
cboTableName.AddItem snpData("table name")
End If
snpData.MoveNext
Loop
snpData.Close
Else
intResult = MsgBox("Could not send the SQL statement to the Excel Database." & vbCrLf & Error(Err), 16, "Excel Demo")
End If
Set snpData = Nothing
End If
End Sub
The above code should be sufficient for the Initialize button to work – you may need to turn off Design Mode in Excel for the button to work.
Now we need to work on the recursion when a table is selected (and the rest of the code):
Private Function FindRelatedTables(strTableName As String, lngColumn As Long, lngRow As Long, intAliasTableNames As Integer) As Long
Dim i As Integer
Dim lngBoxLeft As Long
Dim lngBoxTop As Long
Dim lngBoxWidth As Long
Dim lngBoxHeight As Long
Dim lngResult As Long
Dim intTableColumns As Integer
Dim intForeignColumns As Integer
Dim intFlag As Integer
Dim strOut As String
Dim strLastTable As String
Dim strSplit() As String 'Use to alias the table names
Dim strPrimary As String 'Stores the aliased table primary key table name
Dim strForeign As String 'Stores the aliased table foreign key table name
Dim strSQL As String
Dim snpData As ADODB.Recordset
Dim snpDataForeign As ADODB.Recordset
On Error Resume Next
If intAliasTableNames = True Then
strPrimary = ""
strSplit = Split(strTableName, "_")
For i = 0 To UBound(strSplit)
strPrimary = strPrimary & Left(strSplit(i), 1)
Next i
End If
Set snpDataForeign = New ADODB.Recordset
Set snpData = New ADODB.Recordset
strSQL = "SELECT" & vbCrLf
strSQL = strSQL & " *" & vbCrLf
strSQL = strSQL & "FROM" & vbCrLf
strSQL = strSQL & " [Data Dict Foreign Keys$]" & vbCrLf
strSQL = strSQL & "WHERE" & vbCrLf
strSQL = strSQL & " [Pkey Table Name] = '" & strTableName & "'" & vbCrLf
strSQL = strSQL & "ORDER BY" & vbCrLf
strSQL = strSQL & " [Fkey Table Name]," & vbCrLf
strSQL = strSQL & " [Position]"
snpDataForeign.Open strSQL, dbExcel
If snpDataForeign.State = 1 Then
If Not snpDataForeign.EOF Then
strOut = strTableName & " " & strPrimary & " :" & vbLf
intTableColumns = 0
intForeignColumns = 0
strLastTable = snpDataForeign("Fkey Table Name")
Do While Not snpDataForeign.EOF
If intAliasTableNames = True Then
strForeign = ""
strSplit = Split(snpDataForeign("Fkey Table Name"), "_")
For i = 0 To UBound(strSplit)
strForeign = strForeign & Left(strSplit(i), 1)
Next i
'Verify that the two aliases are not identical
If strForeign = strPrimary Then
strForeign = strForeign & "F"
End If
strOut = strOut & strPrimary & "." & snpDataForeign("Pkey Column Name") & " = " & strForeign & "." & snpDataForeign("Fkey Column Name") & vbLf
Else
strOut = strOut & snpDataForeign("Pkey Table Name") & "." & snpDataForeign("Pkey Column Name") & " = " & snpDataForeign("Fkey Table Name") & "." & snpDataForeign("Fkey Column Name") & vbLf
End If
intForeignColumns = intForeignColumns + 1
snpDataForeign.MoveNext
'See if we need to retrieve the table columns for the previous table
intFlag = False
If snpDataForeign.EOF Then
intFlag = True
Else
If strLastTable <> snpDataForeign("Fkey Table Name") Then
intFlag = True
End If
End If
If intFlag = True Then
strSQL = "SELECT" & vbCrLf
strSQL = strSQL & " *" & vbCrLf
strSQL = strSQL & "FROM" & vbCrLf
strSQL = strSQL & " [Data Dict Tables$]" & vbCrLf
strSQL = strSQL & "WHERE" & vbCrLf
strSQL = strSQL & " [Table Name] = '" & strLastTable & "'" & vbCrLf
strSQL = strSQL & "ORDER BY" & vbCrLf
strSQL = strSQL & " [Column Id]" ' Sort the rows by the second column in the data source
snpData.Open strSQL, dbExcel
If snpData.State = 1 Then
If Not snpData.EOF Then
strOut = strOut & vbLf & strLastTable & " " & strForeign & " :" & vbLf
intTableColumns = 0
Do While Not snpData.EOF
strOut = strOut & snpData("column name") & vbLf
intTableColumns = intTableColumns + 1
snpData.MoveNext
Loop
End If
End If
snpData.Close
'Create the text box
'Strip off the trailing CrLf
strOut = Left(strOut, Len(strOut) - 1)
lngBoxLeft = lngColumn * 200
lngBoxWidth = 200
lngBoxTop = lngRow * 20
lngBoxHeight = (intTableColumns + intForeignColumns + 3) * 5.5
With Sheets("ExcelQueryOfExcel").Shapes.AddTextbox(msoTextOrientationHorizontal, lngBoxLeft, lngBoxTop, lngBoxWidth, (lngBoxHeight * 2 - 1))
With .TextFrame
If Application.Version <= 11 Then
'There is a 255 character limit on text boxes before Office 2007
.Characters.Text = Left(strOut, 255)
Else
.Characters.Text = strOut
End If
.Characters.Font.Size = 8
End With
.Fill.ForeColor.RGB = RGB(255 - lngColumn * 10, 255 - lngColumn * 10, 255)
End With
'With Sheets("ExcelQueryOfExcel").Shapes.AddTextbox(msoTextOrientationHorizontal, lngBoxLeft, lngBoxTop, lngBoxWidth, (lngBoxHeight * 2 - 1))
' With .TextFrame
' .Characters.Text = strOut
' .Characters.Font.Size = 8
' End With
' .Fill.ForeColor.RGB = RGB(255 - lngColumn * 10, 255 - lngColumn * 10, 255)
'End With
If lngColumn < 6 Then
'Only recursively call to 6 levels
'Recursive call into this function
lngResult = FindRelatedTables(strLastTable, lngColumn + 1, lngRow, intAliasTableNames)
End If
'Prepare for the next text box
strOut = strTableName & " " & strPrimary & " :" & vbLf
intTableColumns = 0
intForeignColumns = 0
lngRow = lngRow + 3
End If
If Not snpDataForeign.EOF Then
strLastTable = snpDataForeign("Fkey Table Name")
End If
If lngRow > 5000 Then
'Protection against a very long wait
Exit Do
End If
Loop
End If
snpDataForeign.Close
End If
Set snpDataForeign = Nothing
Set snpData = Nothing
End Function
Private Sub FindTable()
Dim intAliasTableNames As Integer
Dim intResult As Integer
Dim lngResult As Long
Dim lngRow As Long
Dim strSQL As String
Dim strTableName As String
Dim snpData As ADODB.Recordset
On Error Resume Next
intAliasTableNames = chkAliasNames.Value
lngRow = 6
Sheets("ExcelQueryOfExcel").Range("A" & Format(lngRow) & ":Z" & Format(lngRow + 10000)).Delete Shift:=xlUp
If cboTableName <> "" Then
Application.ScreenUpdating = False
strTableName = cboTableName
Set snpData = New ADODB.Recordset
strSQL = "SELECT" & vbCrLf
strSQL = strSQL & " *" & vbCrLf
strSQL = strSQL & "FROM" & vbCrLf
strSQL = strSQL & " [Data Dict Tables$]" & vbCrLf
strSQL = strSQL & "WHERE" & vbCrLf
strSQL = strSQL & " [Table Name] = '" & strTableName & "'" & vbCrLf
strSQL = strSQL & "ORDER BY" & vbCrLf
strSQL = strSQL & " [Column Id]" ' Sort the rows by the second column in the data source
snpData.Open strSQL, dbExcel
If snpData.State = 1 Then
Sheets("ExcelQueryOfExcel").Cells(lngRow, 1).Value = strTableName
Sheets("ExcelQueryOfExcel").Cells(lngRow, 1).Font.Bold = True
lngRow = lngRow + 1
If Not snpData.EOF Then
Do While Not snpData.EOF
Sheets("ExcelQueryOfExcel").Cells(lngRow, 1).Value = snpData("column name")
lngRow = lngRow + 1
snpData.MoveNext
'Safety net
If lngRow > 1000 Then
Exit Do
End If
Loop
lngResult = FindRelatedTables(strTableName, 1, 5, intAliasTableNames)
End If
snpData.Close
Else
intResult = MsgBox("Could not send the SQL statement to the Excel Database." & vbCrLf & Error(Err), 16, "Excel Demo")
End If
Set snpData = Nothing
End If
Application.ScreenUpdating = True
End Sub
Private Sub cboTableName_Click()
FindTable
End Sub
Private Sub cboTableName_GotFocus()
cboTableName.SelStart = 0
cboTableName.SelLength = Len(cboTableName)
End Sub
Private Sub cboTableName_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If (KeyCode = 13) Or (KeyCode = 8 ) Then
FindTable
End If
End Sub
Private Sub chkAliasNames_Click()
FindTable
End Sub
I will not try here to explain how all of the above code works. I created this demonstration for a presentation that I gave a couple months ago. It really should be easy to understand if you step through the code starting with the cboTableName_Click Sub.
Recent Comments