On Fri, 28 Mar 2008 19:52:01 -0700 (PDT), robboll
<robboll@hotmail.com> 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. It needs to to be
>run at the server and not link to the tables.
>
>First the MS Access routine runs to create three tables. XYZTables,
>XYZFields, and XYZResults and populates XYZTables and XYZFields.
>XYZFields is needed for the Interrogation routine that follows:
>
>Public Sub DocumentTables()
> Dim db As DAO.Database, tbl As DAO.TableDef, fld As DAO.Field
> Dim rstTable As DAO.Recordset, rstField As DAO.Recordset
> Dim t As String, strQuery As String
> Dim strTableSet As String, strFieldSet As String
>
> strSQL = "CREATE TABLE XYZTables " & _
> "(TableName TEXT CONSTRAINT " & _
> " PrimaryKey PRIMARY KEY, " & _
> " TableRecords Number) "
> CurrentDb.Execute strSQL, dbFailOnError
>
> strSQL = "CREATE TABLE XYZFields " & _
> "(TableName CHAR, " & _
> "FieldName CHAR, " & _
> "DataType CHAR, " & _
> "DataSize Number, " & _
> "FieldDesc CHAR, " & _
> "SearchValue CHAR) "
> CurrentDb.Execute strSQL, dbFailOnError
>
> strSQL = "CREATE TABLE XYZResults " & _
> "(TableName CHAR, " & _
> "FieldName CHAR, " & _
> "DataType CHAR, " & _
> "DataSize Number, " & _
> "FieldDesc CHAR, " & _
> "SearchValue CHAR) "
> CurrentDb.Execute strSQL, dbFailOnError
>
> strTableSet = "xyzTables"
> strFieldSet = "xyzFields"
>
> DoCmd.SetWarnings False
> DoCmd.RunSQL "DELETE * FROM [" & strTableSet & "];"
> DoCmd.RunSQL "DELETE * FROM [" & strFieldSet & "];"
> DoCmd.SetWarnings True
>
> Set db = CurrentDb
> Set rstTable = db.OpenRecordset(strTableSet, dbOpenDynaset)
> Set rstField = db.OpenRecordset(strFieldSet, dbOpenDynaset)
>
> Debug.Print
>
> For Each tbl In db.TableDefs
> Debug.Print tbl.Name
> If tbl.Attributes = 0 Then
> With rstTable
> .AddNew
> .Fields("TableName") = tbl.Name
> .Fields("TableRecords") = tbl.RecordCount
> On Error Resume Next
> .Fields("TableDesc") =
>tbl.Properties("Description").Value
> On Error GoTo 0
> .Update
> End With
> For Each fld In tbl.Fields
> 'add new record for each field in each table,
>containing
> 'table, field, data type of field
> With rstField
> .AddNew
> .Fields("TableName").Value = tbl.Name
> .Fields("FieldName").Value = fld.Name
> .Fields("DataType").Value =
>GetFieldDataType(fld.Type)
> .Fields("DataSize").Value = fld.Size
> On Error Resume Next
> .Fields("FieldDesc").Value =
>fld.Properties("Description").Value
> On Error GoTo 0
> .Update
> End With
> Next fld
> End If
> Next tbl
>
> Debug.Print
>
> rstField.Close
> rstTable.Close
> Set fld = Nothing
> Set tbl = Nothing
> Set db = Nothing
>End Sub
>
>'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~
>
>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:") ' This
>prompts user for a value to search for.
>
>
> Set db = CurrentDb
> 'Open the Table/Fields table
>
>
> Set rsXYZFields = db.OpenRecordset("xyzFields", 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
>

On Mar 28, 10:35pm, Tom van Stiphout <no.spam.tom7...@cox.net> wrote:
> On Fri, 28 Mar 2008 19:52:01 -0700 (PDT), robboll
>
> <robb...@hotmail.com> 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. It needs to to be
> >run at the server and not link to the tables.
>
> >First the MS Access routine runs to create three tables. XYZTables,
> >XYZFields, and XYZResults and populates XYZTables and XYZFields.
> >XYZFields is needed for the Interrogation routine that follows:
>
> >Public Sub DocumentTables()
> > Dim db As DAO.Database, tbl As DAO.TableDef, fld As DAO.Field
> > Dim rstTable As DAO.Recordset, rstField As DAO.Recordset
> > Dim t As String, strQuery As String
> > Dim strTableSet As String, strFieldSet As String
>
> > strSQL = "CREATE TABLE XYZTables " & _
> > "(TableName TEXT CONSTRAINT " & _
> > " PrimaryKey PRIMARY KEY, " & _
> > " TableRecords Number) "
> > CurrentDb.Execute strSQL, dbFailOnError
>
> > strSQL = "CREATE TABLE XYZFields " & _
> > "(TableName CHAR, " & _
> > "FieldName CHAR, " & _
> > "DataType CHAR, " & _
> > "DataSize Number, " & _
> > "FieldDesc CHAR, " & _
> > "SearchValue CHAR) "
> > CurrentDb.Execute strSQL, dbFailOnError
>
> > strSQL = "CREATE TABLE XYZResults " & _
> > "(TableName CHAR, " & _
> > "FieldName CHAR, " & _
> > "DataType CHAR, " & _
> > "DataSize Number, " & _
> > "FieldDesc CHAR, " & _
> > "SearchValue CHAR) "
> > CurrentDb.Execute strSQL, dbFailOnError
>
> > strTableSet = "xyzTables"
> > strFieldSet = "xyzFields"
>
> > DoCmd.SetWarnings False
> > DoCmd.RunSQL "DELETE * FROM [" & strTableSet & "];"
> > DoCmd.RunSQL "DELETE * FROM [" & strFieldSet & "];"
> > DoCmd.SetWarnings True
>
> > Set db = CurrentDb
> > Set rstTable = db.OpenRecordset(strTableSet, dbOpenDynaset)
> > Set rstField = db.OpenRecordset(strFieldSet, dbOpenDynaset)
>
> > Debug.Print
>
> > For Each tbl In db.TableDefs
> > Debug.Print tbl.Name
> > If tbl.Attributes = 0 Then
> > With rstTable
> > .AddNew
> > .Fields("TableName") = tbl.Name
> > .Fields("TableRecords") = tbl.RecordCount
> > On Error Resume Next
> > .Fields("TableDesc") =
> >tbl.Properties("Description").Value
> > On Error GoTo 0
> > .Update
> > End With
> > For Each fld In tbl.Fields
> > 'add new record for each field in each table,
> >containing
> > 'table, field, data type of field
> > With rstField
> > .AddNew
> > .Fields("TableName").Value = tbl.Name
> > .Fields("FieldName").Value = fld.Name
> > .Fields("DataType").Value =
> >GetFieldDataType(fld.Type)
> > .Fields("DataSize").Value = fld..Size
> > On Error Resume Next
> > .Fields("FieldDesc").Value =
> >fld.Properties("Description").Value
> > On Error GoTo 0
> > .Update
> > End With
> > Next fld
> > End If
> > Next tbl
>
> > Debug.Print
>
> > rstField.Close
> > rstTable.Close
> > Set fld = Nothing
> > Set tbl = Nothing
> > Set db = Nothing
> >End Sub
>
> >'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~
>
> >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:") ' This
> >prompts user for a value to search for.
>
> > Set db = CurrentDb
> > 'Open the Table/Fields table
>
> > Set rsXYZFields = db.OpenRecordset("xyzFields", 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- 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!

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
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 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/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Thanks! I'll check it out. To answer your question about why one
would need such a routine. I have a situation where I was getting an
error message "The path cannot be found" when trying to open SQL
Server 2000. Researing the error I found that that the error message
was usually associated with ISS. I don't have ISS loaded. Anyway, in
the interest of time I uninstalled/reinstalled SQL Server. After
bringing it back in session the user said that some of his data is
missing. He provided me with two accounts to search for. One that he
can find through his application and one that he cannot -- that he
knows was entered.

This is a very poor designed database which creates tables daily and
does not archive anything. As a result it has grown in excess of 65+
Gig in size. It literally has thousands of tables and the user could
not tell me the approximate date when the missing values were added.
That's when I thought of the MS Access routine that I have used in the
past that works great with MS Access (small DBs). I tried linking to
the SQL database and running it, but the workstation didn't have
enough horsepower to do anything with it.

I will try your routine. Will it work with SQL Server 2000? This
looks great! Thanks for your .

RBolling

On Mar 30, 12:51pm, robboll <robb...@hotmail.com> wrote:
> Thanks! I'll check it out. To answer your question about why one
> would need such a routine. I have a situation where I was getting an
> error message "The path cannot be found" when trying to open SQL
> Server 2000. Researing the error I found that that the error message
> was usually associated with ISS. I don't have ISS loaded. Anyway, in
> the interest of time I uninstalled/reinstalled SQL Server. After
> bringing it back in session the user said that some of his data is
> missing. He provided me with two accounts to search for. One that he
> can find through his application and one that he cannot -- that he
> knows was entered.
>
> This is a very poor designed database which creates tables daily and
> does not archive anything. As a result it has grown in excess of 65+
> Gig in size. It literally has thousands of tables and the user could
> not tell me the approximate date when the missing values were added.
> That's when I thought of the MS Access routine that I have used in the
> past that works great with MS Access (small DBs). I tried linking to
> the SQL database and running it, but the workstation didn't have
> enough horsepower to do anything with it.
>
> I will try your routine. Will it work with SQL Server 2000? This
> looks great! Thanks for your .
>
> RBolling

When I run it using SQL Server 2005 with a SQL Server 2000 database.
Steps:

1: Open SQL Server 2005 and connect to the database.
2: Right-Click the database, select New Query and past the code:
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'abc123'

3: Execute.
Result:

Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'MAX'.
Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near 'APPLY'.
Msg 156, Level 15, State 1, Line 11
Incorrect syntax near the keyword 'FOR'.
Msg 137, Level 15, State 2, Line 15
Must declare the variable '@sql'.

RBollinger

robboll (robboll@hotmail.com) writes:
> This is a very poor designed database which creates tables daily and
> does not archive anything. As a result it has grown in excess of 65+
> Gig in size. It literally has thousands of tables and the user could
> not tell me the approximate date when the missing values were added.
> That's when I thought of the MS Access routine that I have used in the
> past that works great with MS Access (small DBs). I tried linking to
> the SQL database and running it, but the workstation didn't have
> enough horsepower to do anything with it.

As I almost said. "One tends to think that if you need to [do] this, your
database design might [not] be what it ought to be."

(Some words were missing.)

> I will try your routine. Will it work with SQL Server 2000? This
> looks great! Thanks for your .

No, it will not work on SQL 2000 at all, as it uses several features
that were added in SQL 2005. In SQL 2000, you would need to run nested
cursors over the system tables.

You should always state which version of SQL Server you are using.


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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

robboll (robboll@hotmail.com) writes:
> When I run it using SQL Server 2005 with a SQL Server 2000 database.
>...
> 3: Execute.
> Result:
>
> Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 'MAX'.
> Msg 170, Level 15, State 1, Line 6
> Line 6: Incorrect syntax near 'APPLY'.
> Msg 156, Level 15, State 1, Line 11
> Incorrect syntax near the keyword 'FOR'.
> Msg 137, Level 15, State 2, Line 15
> Must declare the variable '@sql'.

Change the compatibility level to 90 with sp_dbcmptlevel. Change it back to
80 when you are done.

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Erland Sommarskog (esquel@sommarskog.se) writes:
> robboll (robboll@hotmail.com) writes:
>> When I run it using SQL Server 2005 with a SQL Server 2000 database.
>>...
>> 3: Execute.
>> Result:
>>
>> Msg 170, Level 15, State 1, Line 1
>> Line 1: Incorrect syntax near 'MAX'.
>> Msg 170, Level 15, State 1, Line 6
>> Line 6: Incorrect syntax near 'APPLY'.
>> Msg 156, Level 15, State 1, Line 11
>> Incorrect syntax near the keyword 'FOR'.
>> Msg 137, Level 15, State 2, Line 15
>> Must declare the variable '@sql'.
>
> Change the compatibility level to 90 with sp_dbcmptlevel. Change it back
> to 80 when you are done.


Or run the query from another database, just adding the database name:

DECLARE @sql nvarchar(MAX)

SELECT @sql = (SELECT ' SELECT ' + quotename(o.name, '''') + ', *' +
' FROM yourdb.dbo.' + quotename(o.name) +
' WHERE @mystring IN (' + cl.collist + 'NULL);'
AS [text()]
FROM yourdb.sys.objects o
CROSS APPLY (SELECT c.name + ', ' AS [text()]
FROM yourdb.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'




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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx