Showing posts with label interrogating. Show all posts
Showing posts with label interrogating. Show all posts

Friday, February 24, 2012

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

Sunday, February 19, 2012

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...lTables.sql.txt
John

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,
RBollinger
Hi
"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

Interrogating lock information...

I'm working on a PB / OLE DB / SQL2000 application architecture. Here is my requirement:

I need to find out if/how I can interrogate a SQL2000 database for its locking information, with a fairly high degree of granularity. That is to say I'd want to extract the following info:

TABLE LOCK:
--> The name of the table thats locked. (this I can get from SYS tables...)

PAGE LOCK:
--> The table and the row or range of rows that are locked.

ROW LOCK:
--> The table and the row (RId or physical key) that is locked.

My understanding is that rows that are locked as part of a page lock, or a row that is rowlocked, is managed internally within the DBMS. Any one of the three lock types will register e.g. on the SYSLOCKS table, but only specifies the name of the locked object (i.e. table name, index, etc...).

Is my only real solution to create my own custom locking info table(s)? I don't strongly favor that sort of solution, because the code to properly manage such a table would be pretty ambitious to write, and prone to corruption at some point no matter how well we build it.

Are there any inventive solutions out there?All the tools to do that are at your finguretips, - PerfMon, DBCC, Profiler, and QA. Look into sp_lock stored procedure that will point you to master.dbo.syslocks and master.dbo.syslockinfo. Look into perfmon counters as well as various profiler templates.