<%@ LANGUAGE="VBScript" %>
<%
db_name=UCase(CStr(Request("db_name")))
If db_name="" Then
db_name="phdb"
End If
owner=UCase(CStr(Request("owner")))
If owner="" Then
owner="PHOFFLINE"
End If
table_name=UCase(CStr(Request("table_name")))
If table_name="" Then
table_name="CALIBRATIONS"
End If
Set Connection = Server.CreateObject("ADODB.Connection")
Connection.Mode = adModeRead
Select Case db_name
Case "PHDB"
Connection.Open phdb_rd
Case "CERN"
Connection.Open cern_rd
Case Else
Connection.Open phdb_rd
End Select
'
' primary keys
'
SQLStmt = " select ac.constraint_name,ac.table_name, "
SQLStmt = SQLStmt & " acc.column_name,acc.position "
SQLStmt = SQLStmt & " from all_constraints ac,all_cons_columns acc "
SQLStmt = SQLStmt & " where ac.owner='" & owner & "' "
SQLStmt = SQLStmt & " and ac.table_name='" & table_name & "' "
SQLStmt = SQLStmt & " and ac.constraint_type='P' "
SQLStmt = SQLStmt & " and ac.table_name=acc.table_name "
SQLStmt = SQLStmt & " and ac.owner=acc.owner "
SQLStmt = SQLStmt & " and ac.constraint_name=acc.constraint_name "
SQLStmt = SQLStmt & " order by ac.constraint_type,ac.constraint_name,acc.position "
Set RSp = Connection.Execute(SQLStmt)
'
' check conditions
'
SQLStmt = " select ac.constraint_name,ac.constraint_type,ac.table_name,ac.search_condition, "
SQLStmt = SQLStmt & " acc.column_name "
SQLStmt = SQLStmt & " from all_constraints ac,all_cons_columns acc "
SQLStmt = SQLStmt & " where ac.owner='" & owner & "' "
SQLStmt = SQLStmt & " and ac.table_name='" & table_name & "' "
SQLStmt = SQLStmt & " and ac.constraint_type='C' "
SQLStmt = SQLStmt & " and ac.table_name=acc.table_name "
SQLStmt = SQLStmt & " and ac.owner=acc.owner "
SQLStmt = SQLStmt & " and ac.constraint_name=acc.constraint_name "
SQLStmt = SQLStmt & " order by ac.constraint_type,ac.constraint_name,acc.position "
Set RSc = Connection.Execute(SQLStmt)
'
' foreign keys
'
SQLStmt = " select ac.constraint_name,ac.constraint_type,ac.table_name, "
SQLStmt = SQLStmt & " ac.r_owner,ac.r_constraint_name,acc.column_name,acc.position,ac1.table_name r_table_name,acc1.column_name r_column_name"
SQLStmt = SQLStmt & " from all_constraints ac,all_cons_columns acc, "
SQLStmt = SQLStmt & " all_constraints ac1,all_cons_columns acc1 "
SQLStmt = SQLStmt & " where ac.table_name='" & table_name & "' "
SQLStmt = SQLStmt & " and ac.owner='" & owner & "' "
SQLStmt = SQLStmt & " and ac.constraint_type='R' "
SQLStmt = SQLStmt & " and ac.table_name=acc.table_name "
SQLStmt = SQLStmt & " and ac.owner=acc.owner and ac.constraint_name=acc.constraint_name "
SQLStmt = SQLStmt & " and ac1.table_name=acc1.table_name and ac1.owner=acc1.owner "
SQLStmt = SQLStmt & " and ac1.constraint_name=acc1.constraint_name and ac.r_owner=ac1.owner "
SQLStmt = SQLStmt & " and ac.r_constraint_name=ac1.constraint_name and acc.position=acc1.position "
SQLStmt = SQLStmt & " order by ac.constraint_type,ac.constraint_name,acc.position "
' Response.Write(SQLStmt & " ")
Set RSf = Connection.Execute(SQLStmt)
'
' additional Indexes
'
SQLStmt = " SELECT dic.index_name,dic.column_name "
SQLStmt = SQLStmt & " FROM dba_ind_columns dic,dba_tab_columns dtc,all_constraints ac "
SQLStmt = SQLStmt & " WHERE dic.table_name=dtc.table_name "
SQLStmt = SQLStmt & " AND dic.column_name=dtc.column_name "
SQLStmt = SQLStmt & " AND dic.table_name='" & table_name & "' "
SQLStmt = SQLStmt & " AND dtc.owner='" & owner & "' "
SQLStmt = SQLStmt & " AND dic.index_name=ac.constraint_name(+) "
SQLStmt = SQLStmt & " AND ac.owner(+)='" & owner & "' "
SQLStmt = SQLStmt & " AND dic.table_name=ac.table_name(+) "
SQLStmt = SQLStmt & " and ac.constraint_name is null "
SQLStmt = SQLStmt & " ORDER BY index_name,column_position "
' Response.Write(SQLStmt & " ")
' Set RSi = Connection.Execute(SQLStmt)
rsiok=false
'
' Number of rows, this may be slow !
'
SQLStmt = " SELECT count(rowid) nrows from " & owner & "." & table_name
' Response.Write(SQLStmt & " ")
Set RSn = Connection.Execute(SQLStmt)
%>
Table Keys
Keys for Table
<%Response.write(CStr(owner) & "." & Cstr(table_name))%>
<%
If Not RSp.EOF Then
%>
Primary Key
<%
Constraint_Old="XXXXXXX"
Do While Not RSp.EOF
If Constraint_Old <> RSp("constraint_name") Then
If Constraint_Old<>"XXXXXXX" Then
'
' close parenthesis
'
Response.write(")" & vBCrLf)
End If
Constraint_Old=RSp("constraint_name")
'
Response.write(Constraint_Old & ": ")
'
' open parenthesis
'
Response.Write("(")
Else
Response.Write(",")
End If
If Not IsNull(RSp("column_name")) Then
column_name=CStr(RSp("column_name"))
Else
column_name="?"
End If
Response.write(column_name)
RSp.Movenext
Loop
'
' close parenthesis
'
Response.write(")")
End If
If Not RSc.EOF Then
%>
Column Value Check
<%
Constraint_Old="XXXXXXX"
Do While Not RSc.EOF
If Constraint_Old <> RSc("constraint_name") Then
If Constraint_Old<>"XXXXXXX" Then
Response.write(" " & vBCrLf)
End If
Constraint_Old=RSc("constraint_name")
'
Response.write(Constraint_Old & ": ")
Else
Response.Write(",")
End If
If Not IsNull(RSc("column_name")) Then
column_name=CStr(RSc("column_name"))
Else
column_name="?"
End If
If Not IsNull(RSc("search_condition")) Then
search_condition=CStr(RSc("search_condition"))
Else
search_condition="?"
End If
Response.write("Column " & column_name & " has to fulfill condition: """)
Response.write(search_condition & """" & vBCrLf)
RSc.Movenext
Loop
End If
If Not RSf.EOF Then
%>
Foreign Key (dependence on other tables)
<%
Constraint_Old="XXXXXXX"
Do While Not RSf.EOF
If Constraint_Old <> RSf("constraint_name") Then
If Constraint_Old<>"XXXXXXX" Then
'
' close parenthesis
'
this_table=this_table & ")"
other_table=other_table & ")"
Response.write(Constraint_Old & ": ")
Response.write(owner & "." & table_name & " ")
Response.write(this_table & vBCrLf)
Response.write(" refers to ")
Response.write(r_owner & "." & r_table_name & " ")
Response.write(other_table & " " & vBCrLf)
End If
Constraint_Old=RSf("constraint_name")
r_owner=CStr(RSf("r_owner"))
r_table_name=CStr(RSf("r_table_name"))
this_table="("
other_table="("
Else
this_table=this_table & ","
other_table=other_table & ","
End If
If Not IsNull(RSf("column_name")) Then
column_name=CStr(RSf("column_name"))
Else
column_name="?"
End If
If Not IsNull(RSf("r_column_name")) Then
r_column_name=CStr(RSf("r_column_name"))
Else
r_column_name="?"
End If
this_table=this_table & column_name
other_table=other_table & r_column_name
RSf.Movenext
Loop
this_table=this_table & ")"
other_table=other_table & ")"
Response.write(Constraint_Old & ": ")
Response.write(owner & "." & table_name & this_table & vBCrLf)
Response.write(" refers to ")
Response.write(r_owner & "." & r_table_name & other_table & " " & vBCrLf)
End If
If rsiok Then
If Not RSi.EOF Then
%>
Additional Indexes
<%
index_name_Old="XXXXXXX"
Do While Not RSi.EOF
If index_name_Old <> RSi("index_name") Then
If index_name_old<>"XXXXXXX" Then
'
' close parenthesis
'
this_index=this_index & ")"
Response.write(Index_Name_Old & ": ")
Response.write(this_index & " " & vBCrLf)
End If
index_name_old=RSi("index_name")
this_index="("
Else
this_index=this_index & ","
End If
If Not IsNull(RSi("column_name")) Then
column_name=CStr(RSi("column_name"))
Else
column_name="?"
End If
this_index=this_index & column_name
RSi.Movenext
this_index=this_index & ")"
Response.write(Index_Name_Old & ": ")
Response.write(this_index & " " & vBCrLf)
Loop
End If
End If
%>
Total number of rows
<%
If Not RSn.EOF Then
Response.Write(RSn("nrows"))
Else
Response.write("Table is empty")
End If
Set RSp=nothing
Set RSc=nothing
Set RSf=nothing
Set RSi=nothing
Set RSn=nothing
Connection.Close
%>