Re: How to Interrogate SQL Server Tables for Specific Values - Here"s

On Mar 28, 10:35=A0pm, Tom van Stiphout wrote:
> On Fri, 28 Mar 2008 19:52:01 -0700 (PDT), robboll
>
> wrote:
>
> You may want to read up on catalog views such as sys.objects and
> sys.columns.
> The creation of XYZ* tables would not be needed.
>
> -Tom.
>
>
>
> >In MS Access I have a routine as follows that interrogates all the
> >tables in the database looking for a certain value.
>
> >I am looking for similar routine for SQL Server. =A0It needs to to be
> >run at the server and not link to the tables.
>
> >First the MS Access routine runs to create three tables. =A0XYZTables,
> >XYZFields, and XYZResults and populates XYZTables and XYZFields.
> >XYZFields is needed for the Interrogation routine that follows:
>
> >Public Sub DocumentTables()
> > =A0 =A0Dim db As DAO.Database, tbl As DAO.TableDef, fld As DAO.Field
> > =A0 =A0Dim rstTable As DAO.Recordset, rstField As DAO.Recordset
> > =A0 =A0Dim t As String, strQuery As String
> > =A0 =A0Dim strTableSet As String, strFieldSet As String
>
> > =A0 =A0strSQL =3D "CREATE TABLE XYZTables " & _
> > =A0 =A0 =A0 =A0 =A0 =A0 "(TableName TEXT CONSTRAINT " & _
> > =A0 =A0 =A0 =A0 =A0 =A0 " PrimaryKey PRIMARY KEY, " & _
> > =A0 =A0 =A0 =A0 =A0 =A0 " TableRecords Number) "
> > =A0 =A0 =A0 =A0 =A0 =A0 CurrentDb.Execute strSQL, dbFailOnError
>
> > =A0 =A0strSQL =3D "CREATE TABLE XYZFields " & _
> > =A0 =A0 =A0 =A0 =A0 =A0 "(TableName CHAR, " & _
> > =A0 =A0 =A0 =A0 =A0 =A0 "FieldName CHAR, " & _
> > =A0 =A0 =A0 =A0 =A0 =A0 "DataType CHAR, " & _
> > =A0 =A0 =A0 =A0 =A0 =A0 "DataSize Number, " & _
> > =A0 =A0 =A0 =A0 =A0 =A0 "FieldDesc CHAR, " & _
> > =A0 =A0 =A0 =A0 =A0 =A0 "SearchValue CHAR) "
> > =A0 =A0 =A0 =A0 =A0 =A0 CurrentDb.Execute strSQL, dbFailOnError
>
> > =A0 =A0strSQL =3D "CREATE TABLE XYZResults " & _
> > =A0 =A0 =A0 =A0 =A0 =A0"(TableName CHAR, " & _
> > =A0 =A0 =A0 =A0 =A0 =A0 "FieldName CHAR, " & _
> > =A0 =A0 =A0 =A0 =A0 =A0 "DataType CHAR, " & _
> > =A0 =A0 =A0 =A0 =A0 =A0 "DataSize Number, " & _
> > =A0 =A0 =A0 =A0 =A0 =A0 "FieldDesc CHAR, " & _
> > =A0 =A0 =A0 =A0 =A0 =A0 "SearchValue CHAR) "
> > =A0 =A0 =A0 =A0 =A0 =A0 CurrentDb.Execute strSQL, dbFailOnError
>
> > =A0 =A0strTableSet =3D "xyzTables"
> > =A0 =A0strFieldSet =3D "xyzFields"
>
> > =A0 =A0DoCmd.SetWarnings False
> > =A0 =A0DoCmd.RunSQL "DELETE * FROM [" & strTableSet & "];"
> > =A0 =A0DoCmd.RunSQL "DELETE * FROM [" & strFieldSet & "];"
> > =A0 =A0DoCmd.SetWarnings True
>
> > =A0 =A0Set db =3D CurrentDb
> > =A0 =A0Set rstTable =3D db.OpenRecordset(strTableSet, dbOpenDynaset)
> > =A0 =A0Set rstField =3D db.OpenRecordset(strFieldSet, dbOpenDynaset)
>
> > =A0 =A0Debug.Print
>
> > =A0 =A0For Each tbl In db.TableDefs
> > =A0 =A0 =A0 =A0Debug.Print tbl.Name
> > =A0 =A0 =A0 =A0If tbl.Attributes =3D 0 Then
> > =A0 =A0 =A0 =A0 =A0 =A0With rstTable
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0.AddNew
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0.Fields("TableName") =3D tbl.Name
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0.Fields("TableRecords") =3D tbl.RecordCou=
nt
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0On Error Resume Next
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0.Fields("TableDesc") =3D
> >tbl.Properties("Description").Value
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0On Error GoTo 0
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0.Update
> > =A0 =A0 =A0 =A0 =A0 =A0End With
> > =A0 =A0 =A0 =A0 =A0 =A0For Each fld In tbl.Fields
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0'add new record for each field in each ta=
ble,
> >containing
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0'table, field, data type of field
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0With rstField
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0.AddNew
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0.Fields("TableName").Value =3D tb=
l.Name
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0.Fields("FieldName").Value =3D fl=
d.Name
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0.Fields("DataType").Value =3D
> >GetFieldDataType(fld.Type)
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0.Fields("DataSize").Value =3D fld=
..Size
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0On Error Resume Next
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0.Fields("FieldDesc").Value =3D
> >fld.Properties("Description").Value
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 On Error GoTo 0
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0.Update
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0End With
> > =A0 =A0 =A0 =A0 =A0 =A0Next fld
> > =A0 =A0 =A0 =A0End If
> > =A0 =A0Next tbl
>
> > =A0 =A0Debug.Print
>
> > =A0 =A0rstField.Close
> > =A0 =A0rstTable.Close
> > =A0 =A0Set fld =3D Nothing
> > =A0 =A0Set tbl =3D Nothing
> > =A0 =A0Set db =3D Nothing
> >End Sub
>
> >'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~=
~=AD~
>
> >Function InterrogateDB()
> >On Error GoTo Err_Line
>
> > =A0 =A0 Dim db As DAO.Database
> > =A0 =A0 Dim rsXYZFields As DAO.Recordset
> > =A0 =A0 Dim mTable As String
> > =A0 =A0 Dim mField As String
> > =A0 =A0 Dim strSQL As String
> > =A0 =A0 Dim strFIND As String
> > =A0 =A0 strFIND =3D InputBox("Enter the field name fragment:") ' This
> >prompts user for a value to search for.
>
> > =A0 =A0 Set db =3D CurrentDb
> > =A0 =A0'Open the Table/Fields table
>
> > =A0 =A0Set rsXYZFields =3D db.OpenRecordset("xyzFields", dbOpenSnapshot)=

>
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0With rsXYZFields
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0.MoveFirst
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0Do Until .EOF
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0mTable =3D "[" & Trim(.Fields(0))=
& "]"
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0mField =3D "[" & Trim(.Fields(1))=
& "]"
>
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0If DCount("*", mTable, mField & " Lik=
e '*" & _
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0strFIND & "*'") > 0 Then
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0strSQL =3D "INSERT INTO xyzResult=
s ( TableName, " &
> >_
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0"FieldName, SearchValue ) VALUES =
( '" & mTable &
> >"', '" & _
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0mField & "', '" & strFIND & "' )"=

> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0db.Execute strSQL, dbFailOnError
>
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0End If
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0.MoveNext
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0Loop
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 End With
> > =A0 =A0 =A0 =A0 =A0 =A0rsXYZFields.Close
> > =A0 =A0 =A0 =A0 =A0 =A0Set rsXYZFields =3D Nothing
> > =A0 =A0 =A0 =A0 =A0 =A0db.Close
> > =A0 =A0 =A0 =A0 =A0 =A0Set db =3D Nothing
> >Exit Function
>
> >Err_Line:
> >MsgBox "Error occurred when inserting record"
> >Resume Next- Hide quoted text -
>
> - Show quoted text -

You're right about the tables -- actually I'm really not sure why it's
in there at all. It's not used. xyzFields is the one that is used
and hat is definitely needed. But the effort here is to use SQL
Server exclusively on the same machine.


If you're really not sure what I am asking: Say you are looking for
the string "abc123" and you need to look in all the tables and columns
for that value. I have the routine to do so in Access as posted. I
am looking for a way to do it using SQL Server. Appriciate any
suggestions!

Re: How to Interrogate SQL Server Tables for Specific Values - Here"s how to do it in MS Access

robboll (robboll@hotmail.com) writes:
> If you're really not sure what I am asking: Say you are looking for
> the string "abc123" and you need to look in all the tables and columns
> for that value. I have the routine to do so in Access as posted. I
> am looking for a way to do it using SQL Server. Appriciate any
> suggestions!

One tends to think that if you need to this, your database design might
be what it ought to be. Since each table should be its own domain, you
should always know in which table to look, assuming that you know the domain
for the value.

Nevertheless, here is a compact way to do this in SQL 2005. (Please
remember to always tell which version of SQL Server you are using):

DECLARE @sql nvarchar(MAX)

SELECT @sql = (SELECT ' SELECT ' + quotename(o.name, '''') + ', *' +
' FROM ' + quotename(o.name) +
' WHERE @mystring IN (' + cl.collist + 'NULL);'
AS [text()]
FROM sys.objects o
CROSS APPLY (SELECT c.name + ', ' AS [text()]
FROM sys.columns c
WHERE c.object_id = o.object_id
AND type_name(c.system_type_id)
LIKE '%char%'
FOR XML PATH('')) AS cl(collist)
WHERE o.type = 'U'
AND cl.collist IS NOT NULL
FOR XML PATH(''))
EXEC sp_executesql @sql, N'@mystring nvarchar(200)', @mystring = N'ALFKI'


The query only examines columns of the type char, varchar, nchar and
nvarchar. I was too lazy to include text and ntext. These types are
deprecated in SQL 2005 anyway.

The query makes use of that you can compose a concatenation with help
of the FOR XML PATH construct. The innermost query composes a column
list for a given table. The outer query takes all tables, and runs the
inner query with help of the CROSS APPLY operator, a proprietary
extension to SQL 2005. The addition of NULL to the list is there to
deal with the trailing comma in the column list.

sp_executesql takes a parameterised SQL statement as its first parameter,
and the parameter list as the second and remaining parameters are defined
from the parameter list. For more info, see
http://www.sommarskog.se/dynamic_sql.html#sp_executesql.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx