Interrogating SQL Server Tables for Specific Values

Does anyone have a routine that interrogates tables looking for
specific values. I as a basic routine that works using MS Access. I
first create a table that has all the table names and field names per
table, like:
xyzField
Table Field
Table1 Field1
Table1 Field2
Table1 Field3
Table2 Field1
Table2 Field2
Table2 Field3
...
xyzResults has three text columns:
TableName, FieldName, SearchValue
The I run the routine:
Function InterrogateDB()
On Error GoTo Err_Line
Dim db As DAO.Database
Dim rsXYZFields As DAO.Recordset
Dim mTable As String
Dim mField As String
Dim strSQL As String
Dim strFIND As String
strFIND = InputBox("Enter the field name fragment:")
Set db = CurrentDb
'Open the Table/Fields table
Set rsXYZFields = db.OpenRecordset("xyzField", dbOpenSnapshot)
With rsXYZFields
.MoveFirst
Do Until .EOF
mTable = "[" & Trim(.Fields(0)) & "]"
mField = "[" & Trim(.Fields(1)) & "]"
If DCount("*", mTable, mField & " Like '*" & _
strFIND & "*'") > 0 Then
strSQL = "INSERT INTO xyzResults ( TableName, " &
_
"FieldName, SearchValue ) VALUES ( '" & mTable &
"', '" & _
mField & "', '" & strFIND & "' )"
db.Execute strSQL, dbFailOnError
End If
.MoveNext
Loop
End With
rsXYZFields.Close
Set rsXYZFields = Nothing
db.Close
Set db = Nothing
Exit Function
Err_Line:
MsgBox "Error occurred when inserting record"
Resume Next
End Function
==============================
It prompts me for the value that I am looking for, then interrogates
all the tables and fields for that value. Is there a procedure
similar to this I can use in SQL Server?
Any help appreciated!
Thanks,
RBollingerHi
"robboll" wrote:
> Does anyone have a routine that interrogates tables looking for
> specific values. I as a basic routine that works using MS Access. I
> first create a table that has all the table names and field names per
> table, like:
> xyzField
> Table Field
> Table1 Field1
> Table1 Field2
> Table1 Field3
> Table2 Field1
> Table2 Field2
> Table2 Field3
> ...
> xyzResults has three text columns:
> TableName, FieldName, SearchValue
> The I run the routine:
> Function InterrogateDB()
> On Error GoTo Err_Line
>
> Dim db As DAO.Database
> Dim rsXYZFields As DAO.Recordset
> Dim mTable As String
> Dim mField As String
> Dim strSQL As String
> Dim strFIND As String
> strFIND = InputBox("Enter the field name fragment:")
> Set db = CurrentDb
> 'Open the Table/Fields table
> Set rsXYZFields = db.OpenRecordset("xyzField", dbOpenSnapshot)
>
> With rsXYZFields
> .MoveFirst
> Do Until .EOF
> mTable = "[" & Trim(.Fields(0)) & "]"
> mField = "[" & Trim(.Fields(1)) & "]"
> If DCount("*", mTable, mField & " Like '*" & _
> strFIND & "*'") > 0 Then
> strSQL = "INSERT INTO xyzResults ( TableName, " &
> _
> "FieldName, SearchValue ) VALUES ( '" & mTable &
> "', '" & _
> mField & "', '" & strFIND & "' )"
> db.Execute strSQL, dbFailOnError
> End If
> .MoveNext
> Loop
> End With
> rsXYZFields.Close
> Set rsXYZFields = Nothing
> db.Close
> Set db = Nothing
> Exit Function
>
> Err_Line:
> MsgBox "Error occurred when inserting record"
> Resume Next
> End Function
> ==============================> It prompts me for the value that I am looking for, then interrogates
> all the tables and fields for that value. Is there a procedure
> similar to this I can use in SQL Server?
> Any help appreciated!
> Thanks,
> RBollinger
>
Have you checked out
http://www.users.drew.edu/skass/sql/SearchAllTables.sql.txt
John