SQL Server SP equivalent

V

vovan

I have a stored procedure which returns table name and column name
containing a particular value. I need to convert it to Access query, but
have no knowledge to do that. I'm asking for help. This is the stored
procedure body:

CREATE PROCEDURE _SearchValueInAllTables
@SearchStr nvarchar(100)
AS
BEGIN

CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue
nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2
nvarchar(110)
SET @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
--Search for all types except money and smallmoney
WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE' AND
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName AND
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' +
QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
)

WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2) AND
TABLE_NAME = PARSENAME(@TableName, 1) AND
DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'int', 'bigint',
'smallint', 'tinyint', 'float', 'decimal', 'real', 'datetime',
'smalldatetime') AND
QUOTENAME(COLUMN_NAME) > @ColumnName
)

IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' +
@ColumnName + ', 3630)
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
)
END
END
END
END

BEGIN
IF ISNUMERIC(@SearchStr ) =1 -- only numeric fields are going to be searched
SET @TableName = ''
--Search for money and smallmoney type
WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE' AND
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName AND
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' +
QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
)

WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN ('money', 'smallmoney')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)

IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
'SELECT ''' + @TableName + '.' + @ColumnName + ''', ' + 'CAST(' +
@SearchStr + ' as varchar)' +
' FROM ' + @TableName + ' (NOLOCK) ' + ' WHERE ' + @ColumnName + ' =
' +
'CAST(' +@SearchStr + ' AS money)'
)
END
END
END


END
--Finally return all found values
SELECT ColumnName AS [Table and Column], ColumnValue AS [Full Value In
Column] FROM #Results ORDER BY ColumnName
GO


Thank you
vovan
 
D

Douglas J. Steele

It's not clear to me whether you want the same capability against a Jet
database (an MDB or MDE), or whether you simply want to be able to use the
SP against SQL Server from Access.

If the latter, you can call SQL Server SPs using a pass-through query. The
only "trick" is that you can't dynamically pass a parameter to a stored
procedure: you need to alter the SQL before running it.

Create a pass-through query that connects to your SQL Server. Have as its
SQL

Call _SearchValueInAllTables ''

For the sake of argument, name the query qrySPSearch

To pass whatever parameter you want to that query, use VBA like:

Sub ChangeSP(SearchString As String)
Dim qdfPassThrough As DAO.QueryDef
Dim strSQL As String

Set qdfPass = CurrentDb().QueryDefs("qrySPSearch")
strSQL = "Call _SearchValueInAllTables '" & _
Replace(SearchString, "'", "''") & "'"
qdfPass = strSQL

End Sub

If, on the other hand, you'll looking for the same functionaliy against a
Jet database, you can't do it strictly using queries: you'll have to use VBA
to traverse through the definition of each table

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


vovan said:
I have a stored procedure which returns table name and column name
containing a particular value. I need to convert it to Access query, but
have no knowledge to do that. I'm asking for help. This is the stored
procedure body:

CREATE PROCEDURE _SearchValueInAllTables
@SearchStr nvarchar(100)
AS
BEGIN

CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue
nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2
nvarchar(110)
SET @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
--Search for all types except money and smallmoney
WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE' AND
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName AND
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' +
QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
)

WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2) AND
TABLE_NAME = PARSENAME(@TableName, 1) AND
DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'int', 'bigint',
'smallint', 'tinyint', 'float', 'decimal', 'real', 'datetime',
'smalldatetime') AND
QUOTENAME(COLUMN_NAME) > @ColumnName
)

IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' +
@ColumnName + ', 3630)
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
)
END
END
END
END

BEGIN
IF ISNUMERIC(@SearchStr ) =1 -- only numeric fields are going to be
searched
SET @TableName = ''
--Search for money and smallmoney type
WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE' AND
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName AND
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' +
QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
)

WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN ('money', 'smallmoney')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)

IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
'SELECT ''' + @TableName + '.' + @ColumnName + ''', ' + 'CAST(' +
@SearchStr + ' as varchar)' +
' FROM ' + @TableName + ' (NOLOCK) ' + ' WHERE ' + @ColumnName + ' =
' +
'CAST(' +@SearchStr + ' AS money)'
)
END
END
END


END
--Finally return all found values
SELECT ColumnName AS [Table and Column], ColumnValue AS [Full Value In
Column] FROM #Results ORDER BY ColumnName
GO


Thank you
vovan
Click to expand...
 
V

vovan

Douglas,
thank you.
I wanted to have a query in Access which will return TableName and
ColumnName with the particular value.
For instance I'm entering 1234.56 value via QuickBooks application interface
into QuickBooks database. That database is not relational database and it
stores the same value in many tables. I'd like to find where. It looks like
QuickBooks stores Check value in different places than Deposit value.
I can link tables in QuickBooks from Access. Now I need some tool to look
for a particalar value in Access db.
For SQL Server as I said I have an SP doing this job. Transfering data from
Access to SQL Server works, but it's very time consuming process - each time
I make a new entry I need to truncate data from SQL Server database and
populate it with data from Access db.
I'd like to work directly with Access to avoid those extra steps

vovan

Douglas J. Steele said:
It's not clear to me whether you want the same capability against a Jet
database (an MDB or MDE), or whether you simply want to be able to use the
SP against SQL Server from Access.

If the latter, you can call SQL Server SPs using a pass-through query. The
only "trick" is that you can't dynamically pass a parameter to a stored
procedure: you need to alter the SQL before running it.

Create a pass-through query that connects to your SQL Server. Have as its
SQL

Call _SearchValueInAllTables ''

For the sake of argument, name the query qrySPSearch

To pass whatever parameter you want to that query, use VBA like:

Sub ChangeSP(SearchString As String)
Dim qdfPassThrough As DAO.QueryDef
Dim strSQL As String

Set qdfPass = CurrentDb().QueryDefs("qrySPSearch")
strSQL = "Call _SearchValueInAllTables '" & _
Replace(SearchString, "'", "''") & "'"
qdfPass = strSQL

End Sub

If, on the other hand, you'll looking for the same functionaliy against a
Jet database, you can't do it strictly using queries: you'll have to use
VBA to traverse through the definition of each table

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


vovan said:
I have a stored procedure which returns table name and column name
containing a particular value. I need to convert it to Access query, but
have no knowledge to do that. I'm asking for help. This is the stored
procedure body:

CREATE PROCEDURE _SearchValueInAllTables
@SearchStr nvarchar(100)
AS
BEGIN

CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue
nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2
nvarchar(110)
SET @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
--Search for all types except money and smallmoney
WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE' AND
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName AND
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' +
QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
)

WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2) AND
TABLE_NAME = PARSENAME(@TableName, 1) AND
DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'int', 'bigint',
'smallint', 'tinyint', 'float', 'decimal', 'real', 'datetime',
'smalldatetime') AND
QUOTENAME(COLUMN_NAME) > @ColumnName
)

IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' +
@ColumnName + ', 3630)
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
)
END
END
END
END

BEGIN
IF ISNUMERIC(@SearchStr ) =1 -- only numeric fields are going to be
searched
SET @TableName = ''
--Search for money and smallmoney type
WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE' AND
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName AND
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' +
QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
)

WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN ('money', 'smallmoney')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)

IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
'SELECT ''' + @TableName + '.' + @ColumnName + ''', ' + 'CAST(' +
@SearchStr + ' as varchar)' +
' FROM ' + @TableName + ' (NOLOCK) ' + ' WHERE ' + @ColumnName + '
= ' +
'CAST(' +@SearchStr + ' AS money)'
)
END
END
END


END
--Finally return all found values
SELECT ColumnName AS [Table and Column], ColumnValue AS [Full Value In
Column] FROM #Results ORDER BY ColumnName
GO


Thank you
vovan
Click to expand...
Click to expand...
 
D

Douglas J. Steele

I'm afraid that it would take me more time than I currently have available
to convert that code to use VBA and DAO.

Conceptually, you can get the type of each of the fields in each of the
tables and create a query that attempts to look the value up in the field:

Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim fldCurr As DAO.Field

Set dbCurr = CurrentDb()
For Each tdfCurr In dbCurr.TableDefs
If (tdfCurr.Attributes And dbSystemObject) = 0 Then
For Each fldCurr In tdfCurr.Fields
Select Case tdfCurr.Type
Case dbBigInt, dbBinary, dbCurrency, dbDecimal, ...
' Create SQL statement with no delimiter
Case dbChar, dbGUID, dbMemo,...
' Create SQL statement with quote delimiter
Case dbDate
' Create SQL statement with # delimiter
Case Else
' Should never happen
End Select
Next fldCurr
End If
Next tdfCurr

If you have specific problems implementing this, feel free to post back.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


vovan said:
Douglas,
thank you.
I wanted to have a query in Access which will return TableName and
ColumnName with the particular value.
For instance I'm entering 1234.56 value via QuickBooks application
interface into QuickBooks database. That database is not relational
database and it stores the same value in many tables. I'd like to find
where. It looks like QuickBooks stores Check value in different places
than Deposit value.
I can link tables in QuickBooks from Access. Now I need some tool to look
for a particalar value in Access db.
For SQL Server as I said I have an SP doing this job. Transfering data
from Access to SQL Server works, but it's very time consuming process -
each time I make a new entry I need to truncate data from SQL Server
database and populate it with data from Access db.
I'd like to work directly with Access to avoid those extra steps

vovan

Douglas J. Steele said:
It's not clear to me whether you want the same capability against a Jet
database (an MDB or MDE), or whether you simply want to be able to use
the SP against SQL Server from Access.

If the latter, you can call SQL Server SPs using a pass-through query.
The only "trick" is that you can't dynamically pass a parameter to a
stored procedure: you need to alter the SQL before running it.

Create a pass-through query that connects to your SQL Server. Have as its
SQL

Call _SearchValueInAllTables ''

For the sake of argument, name the query qrySPSearch

To pass whatever parameter you want to that query, use VBA like:

Sub ChangeSP(SearchString As String)
Dim qdfPassThrough As DAO.QueryDef
Dim strSQL As String

Set qdfPass = CurrentDb().QueryDefs("qrySPSearch")
strSQL = "Call _SearchValueInAllTables '" & _
Replace(SearchString, "'", "''") & "'"
qdfPass = strSQL

End Sub

If, on the other hand, you'll looking for the same functionaliy against a
Jet database, you can't do it strictly using queries: you'll have to use
VBA to traverse through the definition of each table

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


vovan said:
I have a stored procedure which returns table name and column name
containing a particular value. I need to convert it to Access query, but
have no knowledge to do that. I'm asking for help. This is the stored
procedure body:

CREATE PROCEDURE _SearchValueInAllTables
@SearchStr nvarchar(100)
AS
BEGIN

CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue
nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2
nvarchar(110)
SET @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
--Search for all types except money and smallmoney
WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE' AND
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName AND
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' +
QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
)

WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2) AND
TABLE_NAME = PARSENAME(@TableName, 1) AND
DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'int',
'bigint', 'smallint', 'tinyint', 'float', 'decimal', 'real', 'datetime',
'smalldatetime') AND
QUOTENAME(COLUMN_NAME) > @ColumnName
)

IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' +
@ColumnName + ', 3630)
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
)
END
END
END
END

BEGIN
IF ISNUMERIC(@SearchStr ) =1 -- only numeric fields are going to be
searched
SET @TableName = ''
--Search for money and smallmoney type
WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE' AND
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName AND
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' +
QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
)

WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN ('money', 'smallmoney')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)

IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
'SELECT ''' + @TableName + '.' + @ColumnName + ''', ' + 'CAST(' +
@SearchStr + ' as varchar)' +
' FROM ' + @TableName + ' (NOLOCK) ' + ' WHERE ' + @ColumnName + '
= ' +
'CAST(' +@SearchStr + ' AS money)'
)
END
END
END


END
--Finally return all found values
SELECT ColumnName AS [Table and Column], ColumnValue AS [Full Value In
Column] FROM #Results ORDER BY ColumnName
GO


Thank you
vovan
Click to expand...
Click to expand...
Click to expand...
 
G

Gary Walter

vovan said:
Douglas,
thank you.
I wanted to have a query in Access which will return TableName and
ColumnName with the particular value.
For instance I'm entering 1234.56 value via QuickBooks application
interface into QuickBooks database. That database is not relational
database and it stores the same value in many tables. I'd like to find
where. It looks like QuickBooks stores Check value in different places
than Deposit value.
I can link tables in QuickBooks from Access. Now I need some tool to look
for a particalar value in Access db.
For SQL Server as I said I have an SP doing this job. Transfering data
from Access to SQL Server works, but it's very time consuming process -
each time I make a new entry I need to truncate data from SQL Server
database and populate it with data from Access db.
I'd like to work directly with Access to avoid those extra steps
Click to expand...
Hi vovan,

Like Douglas, I don't have the time to write specific code,
but here be the complete code for an Access form I use
to quickly search our 4GB+ SQL Server db for a *text* string.

The form has 3 checkboxes to set whether I want to search
tables, stored procs, or views (so obviously you would only
need "tables"), a textbox that you enter search value, and a
label that updates you on the search.

It searches for any occurance of the string within a text field
(so obviously you would want to change to search only *number*
fields for the *exact* search value -- no wildcards)

It saves the search results in an Excel file, but you could change
to save in table instead...

This was adapted from a vb project on

http://www.planet-source-code.com/

but I regret I cannot find the original project so cannot
give credit to original programmer. I believe it might
have been Carlos Alvarado only because he now
has something "similar" for DTS.

I did not spend much time on this. At the time I just needed
to find "something," so not much error code checking.

But, it worked for me (Access 2000 and XP) and was pretty snappy!

Well...here's the code (maybe will help you):

Option Compare Database
Option Explicit
'Declare module level variables
Private m_objServer As SQLDMO.SQLServer
Private m_objDatabase As SQLDMO.Database
Private m_objExcel As Excel.Application
Private m_objWBook As Excel.Workbook
Private m_objWSheet As Excel.Worksheet
Private m_strServer As String
Private m_strUserName As String
Private m_strPassword As String
Private m_strDatabase As String
Private m_strConnectionString As String
Private m_strExportFilePath As String

Private Sub Form_Load()
'*** fill in your own xxx values ****
' (or set these vars however you think best)

m_strServer = "xxxx"
m_strUserName = "xxxx"
m_strPassword = "xxxx"
m_strDatabase = "xxxx"
m_strConnectionString = "Provider=SQLOLEDB.1;" _
& "Password=" & m_strPassword & ";" _
& "Persist Security Info=True;" _
& "User ID=" & m_strUserName & ";" _
& "Initial Catalog=" & m_strDatabase & ";" _
& "Data Source=" & m_strServer
m_strExportFilePath = "C:\SQLSearch.xls"

Me!txtServer = m_strServer
Me!txtUserName = m_strUserName
Me!txtPassword = m_strPassword
Me!txtDatabase = m_strDatabase

If Not ConnectToServer = True Then
MsgBox "Could not connect to server."
End If
End Sub

Private Function ConnectToServer() As Boolean
'Declare local variables
Dim blnReturn As Boolean
'Set the default return value
blnReturn = False
'Create new server object
Set m_objServer = New SQLDMO.SQLServer
'Connect to the server
m_objServer.Connect m_strServer, m_strUserName, m_strPassword
'Check we are connected
If m_objServer.ConnectionID <> 0 Then
'Find our database
Set m_objDatabase = m_objServer.Databases(m_strDatabase)
'Connected OK
blnReturn = True
Else
'Error connecting
MsgBox "Can not connect to server " & m_strServer & vbCrLf & _
"Using the following details " & vbCrLf & _
"UserName: " & m_strUserName & vbCrLf & _
"Password: " & m_strPassword & vbCrLf & _
"Application will now terminate", _
vbOKOnly + vbExclamation
blnReturn = False

End If

ConnectToServer = blnReturn

End Function

Private Sub cmdSearch_Click()

Dim strSearchString As String
Const vbUnchecked = 0
Const vbChecked = -1

'Get the value from the form
strSearchString = Trim(Me!txtSearch & "")

'Check for a search string
If Len(strSearchString) = 0 Then

MsgBox "You must enter a string to search for", vbExclamation
Me!txtSearch.SetFocus
Exit Sub

End If

'Check we have objects to search
If Me!chkSP.Value = vbUnchecked And _
Me!chkView.Value = vbUnchecked And _
Me!chkTable.Value = vbUnchecked Then

MsgBox "You must select the objects you wish to search",
vbExclamation
Me!chkSP.SetFocus
Exit Sub

End If

DoCmd.Hourglass True

'Hide the controls
Me!lblCaption.Visible = False
Me!txtSearch.Visible = False
DoEvents

'Show the label
Me!lblSearch.Visible = True
DoEvents

If Me!chkSP.Value = vbChecked Then

'Search the stored procedures
Call SearchStoredProcedures(strSearchString)

End If

If Me!chkView.Value = vbChecked Then

'Search the views
Call SearchViews(strSearchString)

End If

If Me!chkTable.Value = vbChecked Then

'Search the tables
Call SearchTables(strSearchString)

End If

If Not m_objExcel Is Nothing Then

'Autofit the columns
m_objWSheet.Columns.AutoFit

'Kill the file if it already exists
If Dir(m_strExportFilePath) > vbNullString Then
Kill m_strExportFilePath
End If

'Save the workbook
With m_objWBook
.SaveAs m_strExportFilePath
.Close
End With

'Close Excel
m_objExcel.Quit

'Kill the objects
Set m_objWSheet = Nothing
Set m_objWBook = Nothing
Set m_objExcel = Nothing

'File exported
MsgBox "Instances of " & strSearchString & " exported to " & _
m_strExportFilePath, vbInformation

Else

'No instances found
MsgBox "No instances of " & strSearchString & " found", _
vbInformation


End If

'Hide the label
lblSearch.Caption = vbNullString
lblSearch.Visible = False
DoEvents

'Show the controls
Me!lblCaption.Visible = True
Me!txtSearch.Visible = True
DoEvents

DoCmd.Hourglass False

End Sub

Private Sub SearchStoredProcedures(ByVal v_strSearchString As String)

'Declare local variables
Dim objSP As SQLDMO.StoredProcedure

For Each objSP In m_objDatabase.StoredProcedures

'Update the label
Call UpdateLabel(v_strSearchString, objSP.Name)

'Search for the string
If InStr(1, objSP.Text, v_strSearchString, vbTextCompare) > 0 Then

'String found
Call WriteToExcel(v_strSearchString, objSP.Name, "", "Stored
Procedure")

End If

Next objSP

'Destory the local object
If Not objSP Is Nothing Then
Set objSP = Nothing
End If

End Sub

Private Sub SearchViews(ByVal v_strSearchString As String)

'Declare local variables
Dim objView As SQLDMO.View

For Each objView In m_objDatabase.Views

'Update the label
Call UpdateLabel(v_strSearchString, objView.Name)

'Search for the string
If InStr(1, objView.Text, v_strSearchString, vbTextCompare) > 0 Then

'String found
Call WriteToExcel(v_strSearchString, objView.Name, "", "View")

End If

Next objView

'Destory the local object
If Not objView Is Nothing Then
Set objView = Nothing
End If

End Sub

Private Sub SearchTables(ByVal v_strSearchString As String)

Dim cnnADO As ADODB.Connection
Dim rstData As ADODB.Recordset
Dim objTable As SQLDMO.Table
Dim objColumn As SQLDMO.Column
Dim strSQL As String

On Error Resume Next

'Create the the ADO connection
Set cnnADO = New ADODB.Connection
cnnADO.Open m_strConnectionString

'Create the recordset
Set rstData = New ADODB.Recordset
rstData.ActiveConnection = cnnADO

For Each objTable In m_objDatabase.Tables

'Search for the string
For Each objColumn In objTable.Columns

'Update the label
Call UpdateLabel(v_strSearchString, objTable.Name & "." &
objColumn.Name)

'Only look for string fields
Select Case objColumn.DataType

Case "char", "nchar", "ntext", "nvarchar", "text", "varchar"

'Search for the string
strSQL = "SELECT * " & _
"FROM " & objTable.Name & " " & _
"WHERE " & objColumn.Name & " LIKE '%" &
v_strSearchString & "%'"

'Open the recordset
With rstData
.Source = strSQL
.Open
End With

If Not rstData.EOF Then

'String found
Call WriteToExcel(v_strSearchString, objTable.Name,
objColumn.Name, "Table")

End If

'Close the recordset
rstData.Close

End Select

Next objColumn

Next objTable

'Destory the local objects
If Not rstData Is Nothing Then

If rstData.State = adStateOpen Then
rstData.Close
End If

Set rstData = Nothing

End If

If Not cnnADO Is Nothing Then

If cnnADO.State = adStateOpen Then
cnnADO.Close
End If

Set cnnADO = Nothing

End If

If Not objTable Is Nothing Then
Set objTable = Nothing
End If

If Not objColumn Is Nothing Then
Set objColumn = Nothing
End If

End Sub

Private Sub UpdateLabel(v_strSearchString, v_strObjectName)

'Update the label
lblSearch.Caption = "SEARCHING FOR: " & vbCrLf & vbCrLf & _
v_strSearchString & vbCrLf & vbCrLf & _
"IN" & vbCrLf & vbCrLf & _
v_strObjectName
DoEvents

End Sub

Private Sub WriteToExcel(ByVal v_strSearchString As String, _
ByVal v_strObjectName As String, _
ByVal v_strFieldName As String, _
ByVal v_strObjectType As String)


'Declare local variables
Static intRow As Integer

If m_objExcel Is Nothing Then

'Create excel object
Set m_objExcel = New Excel.Application
Set m_objWBook = m_objExcel.Workbooks.Add
Set m_objWSheet = m_objWBook.Worksheets(1)

'set the row counter
intRow = 1

'Set up worksheet
With m_objWSheet
.Range("A" & intRow).Value = "Search Text"
.Range("B" & intRow).Value = "Object Name"
.Range("C" & intRow).Value = "Field Name"
.Range("D" & intRow).Value = "Object Type"
.Range("A" & intRow, "D" & intRow).Font.Bold = True
.Range("A" & intRow, "D" & intRow).Interior.ColorIndex = 15
End With

'Increment the row counter
intRow = intRow + 1

End If

'Add the values
With m_objWSheet
.Cells(intRow, 1) = v_strSearchString
.Cells(intRow, 2) = v_strObjectName
.Cells(intRow, 3) = v_strFieldName
.Cells(intRow, 4) = v_strObjectType
End With

'Increment the row counter
intRow = intRow + 1

End Sub

Private Sub DisconnectFromServer()

If Not m_objServer Is Nothing Then

'Disconnect from the server
m_objServer.Disconnect

'Destroy module level objects
Set m_objDatabase = Nothing
Set m_objServer = Nothing

End If

End Sub

Private Sub Form_Unload(Cancel As Integer)

Call DisconnectFromServer

End Sub

Private Sub cmdQuit_Click()
On Error GoTo Err_cmdQuit_Click


DoCmd.Quit

Exit_cmdQuit_Click:
Exit Sub

Err_cmdQuit_Click:
MsgBox Err.Description
Resume Exit_cmdQuit_Click

End Sub
 
G

Gary Walter

Hi vovan,

You've probably moved on, but just in case...

the solution I gave you uses SQLDMO which
means you would need to set a Reference to:

C:\Program Files\Microsoft SQL Server\80\Tools\Binn\sqldmo.dll

which means you would need SQL Server 2000 in some
form or another installed on computer you run this Access
code from.

If you are like me, I have 2K Developer edition on my
computers at home and at work. I have not jumped into 2005
new version yet so don't know if will work for those versions.

good luck,

gary
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

SQL IN problem 1
speed-up question 4
SQL Server Mobile Edition does not support calls to HasRows propertyif the underlying cursor is not 4
"IF" test in SP evals to True and False at same time using DataAdapter.FillSchema() 1
"IF" test in SP evals to True and False at same time using DataAdapter.FillSchema() 7

Top